CODEWITHSUNDEEP
sqlite
Alexey Starinsky
Alexey Starinsky

Reputation: 4339

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?

Upvotes: 1

Views: 35

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

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');

Upvotes: 2

Related Questions