Can column constraints be altered after creation in SQLite?

Question

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?

Answer 1

ALTER TABLE in sqlite 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