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

by

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.

More Related Contents:

  1. Sqlite primary key on multiple columns
  2. How do I DROP a constraint from a sqlite (3.6.21) table?
  3. SQLite how to refer to column name by a query
  4. How do I unlock a SQLite database?
  5. “Insert if not exists” statement in SQLite
  6. SQLite Concurrent Access
  7. System.Data.SQLite Close() not releasing database file
  8. INSERT IF NOT EXISTS ELSE UPDATE?
  9. Sqlite LIMIT / OFFSET query
  10. Set start value for AUTOINCREMENT in SQLite
  11. How to use ROW_NUMBER in sqlite
  12. Android – Pulling SQlite database android device
  13. Delete column from SQLite table
  14. ALTER COLUMN in sqlite
  15. SQLite error ‘attempt to write a readonly database’ during insert?
  16. Efficient paging in SQLite with millions of records
  17. Sqlite group_concat ordering
  18. Encrypted cookies in Chrome
  19. How to get a list of column names
  20. is there a limit to the size of a SQLite database?
  21. sqlite – How to get INSERT OR IGNORE to work
  22. How do I add a new column in between two columns?
  23. Creating stored procedure and SQLite?
  24. How Scalable is SQLite? [closed]
  25. change sqlite file size after “DELETE FROM table”
  26. How to set unique constraint over multiple columns when any one can be null?
  27. sql query – how to apply limit within group by
  28. Unable to load DLL sqlite3 in Universal Windows App running on Mobile
  29. Drop column from SQLite table
  30. Creating stored procedure in SQLite

Leave a Comment