Primary key is not unique in SQLite - Stack Overflow

admin管理员组

文章数量:1279055

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?

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?

• sqlite

Share Improve this question Follow edited Feb 24 at 4:45 marc_s 755k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Feb 23 at 22:19 Alexey StarinskyAlexey Starinsky 4,33933 gold badges3232 silver badges7070 bronze badges 2

• 1 I think this is working because NULL is not equal to itself, so they're not treated as duplicates. The id column should probably be AUTO_INCREMENT to ensure it's unique. – Barmar Commented Feb 23 at 23:10
• 1 And if you have an AUTO_INCREMENT column, it should normally be the primary key by itself. If you want a to be unique as well, declare that as a separate key. – Barmar Commented Feb 23 at 23:12

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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

本文标签： Primary key is not unique in SQLiteStack Overflow