CODEWITHSUNDEEP
sqliteunique-constraint
Alex Zaitsev
Alex Zaitsev

Reputation: 1781

sqlite multiple unique isn't work

I have a table:

CREATE TABLE IF NOT EXISTS city_recent
            (_id INTEGER PRIMARY KEY AUTOINCREMENT,
            city_id INTEGER NOT NULL,
            language BOOL NOT NULL,
            type BOOL NOT NULL,
            FOREIGN KEY (city_id) REFERENCES city(_id),
            UNIQUE(city_id, type) ON CONFLICT IGNORE)

But unique don't work:

screenshot

Upvotes: 1

Views: 216

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169304

I have tested your code and it works as expected (test shown below). Most likely what has happened is that the table was created beforehand without the UNIQUE constraint. Try removing IF NOT EXISTS to confirm.

>>> import sqlite3
>>> con = sqlite3.connect(':memory:')
>>> con.execute('''CREATE TABLE IF NOT EXISTS city_recent
...             (_id INTEGER PRIMARY KEY AUTOINCREMENT,
...             city_id INTEGER NOT NULL,
...             language BOOL NOT NULL,
...             type BOOL NOT NULL,
...             FOREIGN KEY (city_id) REFERENCES city(_id),
...             UNIQUE(city_id, type) ON CONFLICT IGNORE);''')
<sqlite3.Cursor object at 0x01298FA0>
>>> con.execute('insert into city_recent(city_id,language,type) values (0,0,1);')
<sqlite3.Cursor object at 0x0129F120>
>>> con.execute('insert into city_recent(city_id,language,type) values (0,0,1);')
<sqlite3.Cursor object at 0x01298FA0>
>>> con.execute('select * from city_recent').fetchall()
[(1, 0, 0, 1)] # -> note that there is only one row in the table

Upvotes: 3

Related Questions