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;

More Related Contents:

  1. “Insert if not exists” statement in SQLite
  2. Sqlite primary key on multiple columns
  3. Add ON DELETE CASCADE behavior to an sqlite3 table after it has been created
  4. SQLite how to refer to column name by a query
  5. How do I unlock a SQLite database?
  6. How do I make an UPDATE while joining tables on SQLite?
  7. SQLite Concurrent Access
  8. System.Data.SQLite Close() not releasing database file
  9. Escape single quote character for use in an SQLite query
  10. INSERT IF NOT EXISTS ELSE UPDATE?
  11. Sqlite LIMIT / OFFSET query
  12. Set start value for AUTOINCREMENT in SQLite
  13. How to use ROW_NUMBER in sqlite
  14. Android – Pulling SQlite database android device
  15. ALTER COLUMN in sqlite
  16. SQLite error ‘attempt to write a readonly database’ during insert?
  17. Efficient paging in SQLite with millions of records
  18. Sqlite group_concat ordering
  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. What is causing Foreign Key Mismatch error?
  23. Creating stored procedure and SQLite?
  24. How Scalable is SQLite? [closed]
  25. How do DATETIME values work in SQLite?
  26. change sqlite file size after “DELETE FROM table”
  27. How to set unique constraint over multiple columns when any one can be null?
  28. sql query – how to apply limit within group by
  29. Drop column from SQLite table
  30. Creating stored procedure in SQLite

Leave a Comment