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

sqlite constraints ddl
Dane O'Connor picture Dane O'Connor · Dec 11, 2009 · Viewed 32.8k times · Source

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

paxdiablo picture paxdiablo · Dec 11, 2009

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;

Related questions

"Insert if not exists" statement in SQLite

I have an SQLite database. I am trying to insert values (users_id, lessoninfo_id) in table bookmarks, only if both do not exist before in a row. INSERT INTO bookmarks(users_id,lessoninfo_id) VALUES( (SELECT _id FROM Users …

Read More
Conditional SQLite check constraint?

I have a table defined by the following SQL: CREATE TABLE test ( id integer PRIMARY KEY NOT NULL UNIQUE, status text NOT NULL, enddate date, /* Checks */ CHECK (status IN ("Current", "Complete")) ); I'd like to add a constraint that requires enddate …

Read More
Is there a way to get the constraints of a table in SQLite?

The command "pragma table_info('tablename')" lists the columns information and "pragma foreign_key_list('tablename')" the foreign keys. How can I display other constraints (check, unique) of a table? Only parsing the field "sql" of the table "sqlite_master"?

Read More