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

Doretheadoretta

UNIQUE is missing a comma before it starts.. – Payola

Answer 1

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

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

Apanage

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? – Orebro

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. – Sundown

apparently because i have NULL columns, and that just shoots the unique check out the window – Sundown

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 – Westphalia

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. – Multilateral

@Multilateral It seems? How did you find it to be ignored? – District

@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 – Multilateral

sqlite treats all NULL values as different, so if your unique columns contain NULLs those rows might be duplicated. – Blackberry

@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. – Manhood

Answer 2

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);

Denaturalize

This worked for me on android 10 since it seems that adding UNIQUE(i, j) to the create statement is completely ignored on android. – Multilateral

Answer 3

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

Late

I initially did that...didn't work. I tried it again just in case...still doesn't work – Doretheadoretta

It should be CREATE TABLE NAME (column defs..., CONSTRAINT constraint_name unique(col1, col2)) – Shirty

Answer 4

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.

Gussi

Answer 5

it Works 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)
);

Strongroom