Java DB - Why can't I create this table with these foreign key constraints?

Question

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 be missing something fundamental about SQL databases. What?

Answer 1

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...