How do I DROP a constraint from a sqlite (3.6.21) table?

Question

I have the following table:

CREATE TABLE child(    id INTEGER PRIMARY KEY,    parent_id INTEGER CONSTRAINT parent_id REFERENCES parent(id),    description TEXT); 

How do I drop the constraint?

Answer 1

SQLite does not (as of this answer) support the alter table drop constraint command. The allowed syntax can be seen here. You will need to create a new table without a constraint, transfer the data, then delete the old table.

I think something like the following should work:

CREATE TABLE child2 (      id          INTEGER PRIMARY KEY,      parent_id   INTEGER,     description TEXT ); INSERT INTO child2 (id, parent_id, description)    SELECT id, parent_id, description FROM CHILD; DROP TABLE child; ALTER TABLE child2 RENAME TO child; 

Note that the insert into could probably be simplified to not use explicit column names but I've left it that way in case you want to change the structure as well.

For example, if you're removing the constraint on the parent_id column, it's of dubious use to keep it there at all. In that case, you could modify the data transfer to:

CREATE TABLE child2 (id INTEGER PRIMARY KEY, description TEXT); INSERT INTO child2 (id, description) SELECT id, description FROM CHILD; 

Answer 2

I think this is an easier, more concise approach:

copy db.sqlite3 backup-db.sqlite3 echo .dump tablename | sqlite3 db.sqlite3 > modify.sql (now delete or change the constraint in modify.sql) echo drop table tablename; | sqlite3 db.sqlite3  sqlite3 db.sqlite3 < modify.sql 

You can now redump the new database table and compare the differences.