I am trying to create three tables:
Users
- username
- userID
Categories
- category
- categoryID
- userID (references user table)
Expenses
- amount
- date
- expenseID
- categoryID (references category table) NOT WORKING!
- userID (references user table) NOT WORKING!
Here is the code of my create statements. Everything worked fine until I added the "ON DELETE..." clauses to the expenses table.
private final static String createUsersTable =
"CREATE TABLE users ("+
"userID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "+
"username VARCHAR(255) NOT NULL UNIQUE " +
")";
private final static String createCategoriesTable =
"CREATE TABLE categories ("+
"categoryID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "+
"category VARCHAR(255) NOT NULL, " +
"userID INTEGER NOT NULL, " +
"FOREIGN KEY (userID) REFERENCES users (userID) ON DELETE CASCADE"+
")";
private final static String createExpensesTable =
"CREATE TABLE expenses ("+
"expenseID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "+
"amount INTEGER NOT NULL, "+
"date VARCHAR(255) NOT NULL, "+
"categoryID INTEGER, "+
"userID INTEGER NOT NULL, "+
"FOREIGN KEY (categoryID) REFERENCES categories (categoryID) ON DELETE SET NULL, "+
"FOREIGN KEY (userID) REFERENCES users (userID) ON DELETE CASCADE"+
")";
This is the exception that I get when I try to create the table:
java.sql.SQLSyntaxErrorException: Foreign Key 'SQL110915104641092' is invalid because 'The table cannot be defined as a dependent of table ME.USERS because of delete rule restrictions. (The relationship causes the table to be delete-connected to the indicated table through multiple relationships and the delete rule of the existing relationship is SET NULL.). '.
I must 开发者_JAVA技巧be missing something fundamental about SQL databases. What?
expenses
refers to categories
, which refers to users
. So having a foreign key for users
in expenses
is redundant. I believe that the error comes from conflicting actions between SET NULL
and CASCADE
.
When you delete a user
, the categories
referring to it are deleted (because of the CASCADE
) and the categories
foreign key is set to NULL
in expenses
(because of the SET NULL
). But, at the same time, when you delete a user
, the expenses
referring to it are deleted (because of the CASCADE
).
One liner: Remove a users
foreign key in either expenses
or categories
, depending on what you're trying to do with them...
In table Categories
, in addition to the candidate key on categoryID
, create a super key on the compound of categoryID
and userID
. In table Expenses
, reference the compound key only.
精彩评论