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.

Answer 3

With DB Browser for SQLite you can do this by going to Modify Table then clicking on the constraints tab, then clicking Remove constraint.

友情链接