Add ON DELETE CASCADE behavior to sqlite3 table after creating it

Question

Can I add ON DELETE CASCADE to a table after creating it?

My diagram is as follows:

CREATE TABLE skills(name varchar, skill varchar, level int, foreign key(name) references runners(name), primary key(name, skill));

And I would like to cascade if the foreign key is deleted.

Answer 1

SQLite ALTER TABLE cannot do what you want.

However, you can bypass the SQL interpreter and directly modify the internal definition of the table. SQLite stores the table definitions as a text copy of the CREATE TABLE command in the sqlite_master table; check the result of this query:

 SELECT sql FROM sqlite_master WHERE type='table' AND name='skills'; 

Add your cascading specification to this line, then enable writing to sqlite_master using PRAGMA writable_schema=1; and write in it a new table definition

 UPDATE sqlite_master SET sql='...' WHERE type='table' AND name='skills'; 

Then open the database.

WARNING This only works for changes that do not change the format of the table on disk. If you make any changes that change the recording format (for example, adding / removing fields or changing rowid ), your database will explode horribly.