SQLite table constraint - unique on multiple columns
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:
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
Put the UNIQUE declaration within the column definition section; working example:
CREATE TABLE a (
i INT,
j INT,
UNIQUE(i, j) ON CONFLICT REPLACE
);
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
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);
UNIQUE(i, j) to the create statement is completely ignored on android.
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.
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)
);
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- How can I list the tables in a SQLite database file that was opened with ATTACH?
- SQLite - UPSERT *not* INSERT or REPLACE
- How do I specify unique constraint for multiple columns in MySQL?
- Sqlite primary key on multiple columns
- How do I check in SQLite whether a table exists?
- How to get first/top row of the table in Sqlite via Sql Query
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.ON CONFLICT REPLACEit 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 clauseUNIQUE(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.