sqlite

Primary key is not unique in SQLite

The following SQL queries

CREATE TABLE t2
(
    id INTEGER, 
    a INTEGER, 
    b TEXT, 
    PRIMARY KEY(id, a)
);

INSERT INTO t2 (a, b) VALUES (17, 'seven');
INSERT INTO t2 (a, b) VALUES (17, 'seven');

SELECT rowid, * 
FROM t2;

I created a table containing duplicate primary key values:

(1, None, 17, 'seven')
(2, None, 17, 'seven')

What does it mean?

Solution

  • If you look closely at your insert statement, you are inserting NULL as the id value. Rightfully SQLite should not allow NULL as primary key value, but due to a bug it allows it (q.v. here).

    If you use non NULL values it should fail:

    CREATE TABLE t2(id INTEGER, a INTEGER, b TEXT, PRIMARY KEY(id, a));
INSERT INTO t2 (id, a, b) VALUES (1, 17, 'seven');
INSERT INTO t2 (id, a, b) VALUES (1, 17, 'seven');