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

by

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;

Leave a Comment

tech