Altering table's unique field

Question

Hello all,

I have a table which has an unique key with 2 constraints in it. I want to alter the table such that i can remove one of the constraint in unique field there.

My current db schema :

CREATE TABLE testtable(test1 TEXT, test2 TEXT, test3 TEXT, test4 TEXT DEFAULT FALSE,UNIQUE (test1,test2))

I already have this table populated with data and do not want to drop and recreate this table in my next release. Is there a way to alter the table's unique field. I did not set a constraint for my this table.

I want my database schema to be like this after update.

CREATE TABLE testtable(test1 TEXT, test2 TEXT, test3 TEXT, test4 TEXT DEFAULT FALSE,UNIQUE (test1))

Answer 1

In other DBMSes you would have used ALTER TABLE DROP CONSTRAINT .... However sqlite only supports the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command (Source).

Therefore, I'm afraid you'd have to create a new table:

CREATE TABLE testtable2(
   test1 TEXT, test2 TEXT, test3 TEXT, test4 TEXT DEFAULT FALSE,
   UNIQUE (test1)
);

Then insert the data from the old table to the new table:

INSERT INTO testtable2 SELECT * FROM testtable;

Then you can drop the old table:

DROP TABLE testtable;

And finally rename the new table to the original name:

ALTER TABLE testtable2 RENAME TO testtable;

---

UPDATE:

Be careful that your new constraint will be less permissive. For example if you had the following rows in your original table:

test1       test2       test3       test4     
----------  ----------  ----------  ----------
a           a           100         1         
a           b           200         2         
a           c           300         3         

Then the INSERT INTO testtable2 SELECT * FROM testtable; would fail because test1 is not unique:

SQL error: column test1 is not unique