SQLite table constraint - unique on multiple columns

Rich picture Rich · Apr 23, 2010 · Viewed 103.7k times · Source

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

Answer

Ayman Hourieh picture Ayman Hourieh · Apr 23, 2010

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

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