sqliteddl

Add ON DELETE CASCADE behavior to an sqlite3 table after it has been created

Is it possible to add an ON DELETE CASCADE to a table after it has been created?

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

Solution

  • SQLite's ALTER TABLE command cannot do what you want.

    However, it is possible to bypass the SQL interpreter and change the internal table definition directly. SQLite stores table definitions as a textual copy of the CREATE TABLE command in its sqlite_master table; check out the result of this query:

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

    Add your cascade specification to that string, then enable write access to sqlite_master with PRAGMA writable_schema=1; and write your new table definition into it:

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

    Then reopen the database.

    WARNING: This works only for changes that do not change the on-disk format of the table. If you do make any change that changes the record format (such as adding/removing fields, or modifying the rowid), your database will blow up horribly.