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

User · Answer

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

User · Answer

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

User · Answer

Put the UNIQUE declaration within the column definition section  working example   CREATE TABLE a       i INT      j INT      UNIQUE i  j  ON CONFLICT REPLACE

User · Answer

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 gt  CREATE TABLE IF NOT EXISTS t1  id INTEGER PRIMARY KEY  a TEXT UNIQUE  b TEXT   sqlite gt  INSERT INTO t1  a  b  VALUES        gt        Alice    Some title           gt        Bob    Palindromic guy           gt        Charles    chucky cheese           gt        Alice    Some other title           gt      ON CONFLICT a  DO UPDATE SET b excluded b  sqlite gt  CREATE TABLE IF NOT EXISTS t2  id INTEGER PRIMARY KEY  a TEXT UNIQUE  b TEXT  UNIQUE a  ON CONFLICT REPLACE   sqlite gt  INSERT INTO t2  a  b  VALUES        gt        Alice    Some title           gt        Bob    Palindromic guy           gt        Charles    chucky cheese           gt        Alice    Some other title    sqlite gt   mode col sqlite gt   headers on sqlite gt  select   from t1  id          a           b                ----------  ----------  ---------------- 1           Alice       Some other title 2           Bob         Palindromic guy  3           Charles     chucky cheese    sqlite gt  select   from t2  id          a           b               ----------  ----------  --------------- 2           Bob         Palindromic guy 3           Charles     chucky cheese   4           Alice       Some other titl sqlite gt     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

[sqlite] SQLite table constraint - unique on multiple columns

Examples related to sqlite

Examples related to unique-constraint

Examples related to compound-key