SQLite table constraint - unique on multiple columns

Question

I can find syntax "charts" on this on the SQLite website, but no examples and my code is crashing. I have other tables with unique constraints on a single column, but I want to add a constraint to the table on two columns. This is what I have that is causing an SQLiteException with the message "syntax error".

CREATE TABLE name (column defs) 
UNIQUE (col_name1, col_name2) ON CONFLICT REPLACE

I'm doing this based on the following:

table-constraint

To be clear, the documentation on the link I provided says that CONTSTRAINT name should come before my constraint definition.

Something that may lead to the solution though is that whatever follows my parenthesized column definitions is what the debugger complains about.

If I put

...last_column_name last_col_datatype) CONSTRAINT ...

the error is near "CONSTRAINT": syntax error

If I put

...last_column_name last_col_datatype) UNIQUE ...

the error is near "UNIQUE": syntax error

UNIQUE is missing a comma before it starts.. – Majid Bashir Nov 29 '16 at 06:48 — Majid Bashir, Nov 29 '16 at 06:48

score 380 · Accepted Answer · edited May 18 '20 at 07:31

380

Put the UNIQUE declaration within the column definition section; working example:

CREATE TABLE a (
    i INT,
    j INT,
    UNIQUE(i, j) ON CONFLICT REPLACE
);

edited May 18 '20 at 07:31

Martin Tournoij

26,737
24
105
146

answered Apr 23 '10 at 20:50

Ayman Hourieh

132,184
23
144
116

6

Nice answer +1. Does this create syntax allow me to use the regular insert method, and not the insertWithOnConflict with the SQLiteDatabase.CONFLICT_REPLACE flag? – Oleg Belousov Jan 25 '14 at 16:23
4

I'm using `ON CONFLICT IGNORE` (haven't tried replace yet) with more than 2 columns, but I'm not seeing it honor the unique constraint, it just merrily adds the duplicates. – Michael Jul 17 '14 at 00:10
7

apparently because i have NULL columns, and that just shoots the unique check out the window – Michael Jul 17 '14 at 00:14
6

Beware using `ON CONFLICT REPLACE` it may not be what you want--it deletes pre-existing rows to allow the new row to be inserted. Normally, I would want to ABORT or ROLLBACK the constraint violation. [SQLite ON CONFLICT clause](https://sqlite.org/lang_conflict.html) – karmakaze Feb 23 '20 at 02:47
It seems that adding `UNIQUE(i, j)` to the create statement is completely ignored on android. It creates the table, but leaves off the UNIQUE constraint. I was only able to do this by using an index. – gattsbr Aug 11 '20 at 14:55
@gattsbr It seems? How did you find it to be ignored? – Louis CAD Feb 01 '21 at 09:51
@LouisCAD I had found it to be ignored by adding the same data twice, i.e. not unique. If I remember correctly, it did work on ios. using an index instead worked on both however. Oleg's answer worked for me – gattsbr Feb 01 '21 at 19:51
3

sqlite treats all NULL values as different, so if your unique columns contain NULLs those rows might be duplicated. – Pokulo Feb 02 '22 at 08:33
@karmakaze: I agree but sometimes, e.g. if your table is used as a cache, you might want to use `ON CONFLICT REPLACE/IGNORE` depending on your cache policy. Ultimately, the main concern of a unique constraint is to *guarantee the row uniqueness*. – Mustafa Özçetin Aug 02 '23 at 07:25

score 13 · Answer 2 · answered Apr 29 '20 at 16:28

13

If you already have a table and can't/don't want to recreate it for whatever reason, use indexes:

CREATE UNIQUE INDEX my_index ON my_table(col_1, col_2);

answered Apr 29 '20 at 16:28

Oleg Yablokov

736
8
19

This worked for me on android 10 since it seems that adding `UNIQUE(i, j)` to the create statement is completely ignored on android. – gattsbr Aug 11 '20 at 14:54

score 9 · Answer 3 · answered Apr 23 '10 at 20:48

9

Well, your syntax doesn't match the link you included, which specifies:

 CREATE TABLE name (column defs) 
    CONSTRAINT constraint_name    -- This is new
    UNIQUE (col_name1, col_name2) ON CONFLICT REPLACE

answered Apr 23 '10 at 20:48

Larry Lustig

49,320
14
110
160

I initially did that...didn't work. I tried it again just in case...still doesn't work – Rich Apr 23 '10 at 20:48
It should be `CREATE TABLE NAME (column defs..., CONSTRAINT constraint_name unique(col1, col2))` – mmm111mmm Feb 01 '23 at 09:36

score 1 · Answer 4 · answered Jun 16 '19 at 18:39

Be careful how you define the table for you will get different results on insert. Consider the following



CREATE TABLE IF NOT EXISTS t1 (id INTEGER PRIMARY KEY, a TEXT UNIQUE, b TEXT);
INSERT INTO t1 (a, b) VALUES
    ('Alice', 'Some title'),
    ('Bob', 'Palindromic guy'),
    ('Charles', 'chucky cheese'),
    ('Alice', 'Some other title') 
    ON CONFLICT(a) DO UPDATE SET b=excluded.b;
CREATE TABLE IF NOT EXISTS t2 (id INTEGER PRIMARY KEY, a TEXT UNIQUE, b TEXT, UNIQUE(a) ON CONFLICT REPLACE);
INSERT INTO t2 (a, b) VALUES
    ('Alice', 'Some title'),
    ('Bob', 'Palindromic guy'),
    ('Charles', 'chucky cheese'),
    ('Alice', 'Some other title');

$ sqlite3 test.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE IF NOT EXISTS t1 (id INTEGER PRIMARY KEY, a TEXT UNIQUE, b TEXT);
sqlite> INSERT INTO t1 (a, b) VALUES
   ...>     ('Alice', 'Some title'),
   ...>     ('Bob', 'Palindromic guy'),
   ...>     ('Charles', 'chucky cheese'),
   ...>     ('Alice', 'Some other title') 
   ...>     ON CONFLICT(a) DO UPDATE SET b=excluded.b;
sqlite> CREATE TABLE IF NOT EXISTS t2 (id INTEGER PRIMARY KEY, a TEXT UNIQUE, b TEXT, UNIQUE(a) ON CONFLICT REPLACE);
sqlite> INSERT INTO t2 (a, b) VALUES
   ...>     ('Alice', 'Some title'),
   ...>     ('Bob', 'Palindromic guy'),
   ...>     ('Charles', 'chucky cheese'),
   ...>     ('Alice', 'Some other title');
sqlite> .mode col
sqlite> .headers on
sqlite> select * from t1;
id          a           b               
----------  ----------  ----------------
1           Alice       Some other title
2           Bob         Palindromic guy 
3           Charles     chucky cheese   
sqlite> select * from t2;
id          a           b              
----------  ----------  ---------------
2           Bob         Palindromic guy
3           Charles     chucky cheese  
4           Alice       Some other titl
sqlite>

While the insert/update effect is the same, the id changes based on the table definition type (see the second table where 'Alice' now has id = 4; the first table is doing more of what I expect it to do, keep the PRIMARY KEY the same). Be aware of this effect.

score 0 · Answer 5 · answered Oct 25 '21 at 11:37

work for me

create table projects (
    _id integer primary key autoincrement,
    project_type text not null,
    name text not null,
    description text,
    last_updated datetime default current_timestamp,
    date_created datetime default current_timestamp,
    unique (project_type, name)
);

SQLite table constraint - unique on multiple columns

5 Answers5

Linked

Related