204

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

Improve this question
1
  • 1
    UNIQUE is missing a comma before it starts.. Commented Nov 29, 2016 at 6:48

5 Answers 5

Reset to default
392

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

CREATE TABLE a (
    i INT,
    j INT,
    UNIQUE(i, j) ON CONFLICT REPLACE
);
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

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?
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.
apparently because i have NULL columns, and that just shoots the unique check out the window
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
sqlite treats all NULL values as different, so if your unique columns contain NULLs those rows might be duplicated.
|
16

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);
Improve this answer

1 Comment

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

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
)
Improve this answer

3 Comments

I initially did that...didn't work. I tried it again just in case...still doesn't work
It should be CREATE TABLE NAME (column defs..., CONSTRAINT constraint_name unique(col1, col2))
@mmm111mmm edited the answer to have the correct syntax
2

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.

Improve this answer

Comments

1

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)
);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.