Search code examples
sqlsqliteforeign-keys

SQLite multiple constraints on 1 FK

I have a table in SQLite with specified FK as follows: CONSTRAINT "model" FOREIGN KEY("category_id") REFERENCES "category"("id") deferrable initially deferred and I need to add ON DELETE CASCADE. I know that in SQLite you can't add constraint with ALTER so how can I combine these 2 constraints when creating a new table?

Solution

  • In the CREATE statement of the new table use:

    CONSTRAINT model FOREIGN KEY(category_id) REFERENCES category(id) 
ON DELETE CASCADE 
DEFERRABLE INITIALLY DEFERRED

    See the demo.