CODEWITHSUNDEEP
sqlite
Joseph_Marzbani
Joseph_Marzbani

Reputation: 1876

Can column constraints be altered after creation in SQLite?

Say I have created a table like this:

CREATE TABLE table_1(column_1 UNIQUE, column_2 NOT NULL);

Now after I have inserted lots of data, for some reason I need to remove UNIQUE constraint from the first column, that's column_1. Is it possible? If yes, how?

Upvotes: 5

Views: 1316

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

ALTER TABLE in is very limited.

You can however, to a limited extent, change some contraints using CREATE INDEX and CREATE TRIGGER.

You could, eg:

CREATE TABLE table_1(column_1, column_2);
-- No constrains in table_1
CREATE UNIQUE INDEX t1_c1_1 ON table_1 (column_1);
-- From now, column_1 must be UNIQUE
CREATE TRIGGER t1_c2_1i BEFORE INSERT ON table_1 WHEN column_2 IS NULL BEGIN
    SELECT RAISE(ABORT, 'column_2 can not be NULL');
END;
CREATE TRIGGER t1_c2_1u BEFORE UPDATE ON table_1 WHEN column_2 IS NULL BEGIN
    SELECT RAISE(ABORT, 'column_2 can not be NULL');
END;
-- From now, NULL column_2 update/insert will fail
DROP TRIGGER t1_c1_1;
-- Now column_1 doesn't need to be UNIQUE
DROP TRIGGER t1_c2_1i;
DROP TRIGGER t1_c2_1u;
-- Now column_2 may be NULL

Note:

  • you can't delete existing constrains;
  • creating indexes will grow your data (index data);
  • creating trigger may degrade table performance.

Another workaround is duplicating existing table removing constraints:

CREATE TEMP TABLE table_1 AS SELECT * FROM MAIN.table_1;
DROP TABLE table_1;
CREATE TABLE table_1 AS SELECT * FROM TEMP.table1;
-- table_1 is now a copy from initial table_1, but without constraints

Upvotes: 6

CL.
CL.

Reputation: 180060

SQLite's ALTER TABLE statement does not support this.

Your best bet is to export the database as text (.dump in the sqlite3 tool), remove the UNIQUE constrant, and recreate the database.

Upvotes: 5

Related Questions