SQLite primary key on multiple columns

Question

What is the syntax for specifying a primary key on more than 1 column in SQLite ?

This is also called compound key http://en.wikipedia.org/wiki/Compound_key — OneWorld, Jan 07 '13 at 16:36
@OneWorld Or composite key, if any of the columns isn't a key itself. — Michael, Apr 09 '14 at 16:47

score 969 · Accepted Answer · edited Aug 16 '23 at 07:13

969

According to the documentation on CREATE TABLE, specifically table-constraint, it's:

CREATE TABLE something (
  column1, 
  column2, 
  column3, 
  PRIMARY KEY (column1, column2)
);

edited Aug 16 '23 at 07:13

U. Windl

3,480
26
54

answered Apr 09 '09 at 15:21

Brian Campbell

322,767
57
360
340

8

Well, this is right, but according to the documentation, CREATE TABLE something (column1 PRIMARY KEY, column2 PRIMARY KEY); should be possible as well, but it is not. – Yar Mar 23 '11 at 18:20
12

@Yar The docs say "If there is more than one PRIMARY KEY clause in a single CREATE TABLE statement, it is an error." Yes, the railroad diagrams might indicate that is valid as well, but the text below clarifies that it is not. – Brian Campbell Mar 23 '11 at 19:03
18

Remember to add the *PRIMARY KEY(column1, column2)* part at the end like in this answer. If you try to add it after column2 definition you will get a **syntax error**. – vovahost Feb 07 '15 at 19:13
1

@vovahost It's because there are *two different* productions for **PRIMARY KEY**: One is **column-constraint** within **column-def**, and the other is **table-constraint**. Both use different syntax, so only the latter (at the end) allows a list of columns. – U. Windl Aug 16 '23 at 07:09

score 185 · Answer 2 · edited Jun 08 '13 at 23:57

185

CREATE TABLE something (
  column1 INTEGER NOT NULL,
  column2 INTEGER NOT NULL,
  value,
  PRIMARY KEY ( column1, column2)
);

edited Jun 08 '13 at 23:57

Czechnology

14,832
10
62
88

answered Feb 19 '11 at 02:57

xiwok

1,859
1
11
2

Doesn't Primary Key impose a NOT NULL? – pratnala Mar 13 '14 at 18:12
33

@pratnala In standard SQL, yes. In SQLite, `NULL` is allowed in primary keys. This answer emphasizes that if you want more standard behavior, you need to add the `NOT NULL` yourself. My answer is just the very basic syntax for a multi-column primary key. – Brian Campbell Apr 04 '14 at 03:04

score 52 · Answer 3 · edited Jan 10 '13 at 10:51

52

Yes. But remember that such primary key allow NULL values in both columns multiple times.

Create a table as such:

    sqlite> CREATE TABLE something (
column1, column2, value, PRIMARY KEY (column1, column2));

Now this works without any warning:

sqlite> insert into something (value) VALUES ('bla-bla');
sqlite> insert into something (value) VALUES ('bla-bla');
sqlite> select * from something;
NULL|NULL|bla-bla
NULL|NULL|bla-bla

edited Jan 10 '13 at 10:51

Deepzz

4,573
1
28
52

answered Sep 10 '10 at 14:57

jsmarkus

1,482
1
16
21

Is there any reference to the reason of such behavior? What would be a good way to dump several rows in the database and still remove duplicates, even if they contain `NULL`? – Pastafarianist Nov 04 '15 at 22:42
6

@Pastafarianist http://www.sqlite.org/lang_createtable.html - "According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. [...] NULL values are considered distinct from all other values, including other NULLs." – The incredible Jan Jun 26 '17 at 09:32
Yes, in SQL NULLs always compare false. Because of this, relational theory specifically excludes NULL as the value of any key component. SQLite, however, is relational practice. It seems the authors chose to pragmatically allow the multiple but not "equal" keys. Clearly it's preferable not to allow NULLs as key values. – holdenweb Jan 30 '18 at 17:31
Actually *this is not an answer*, but a response to a comment on https://stackoverflow.com/a/3685672/6607497 which is a duplicate of https://stackoverflow.com/a/734704/6607497. Probably too late for the administrators now. – U. Windl Aug 16 '23 at 07:16

compte14031879 · Answer 4 · 2017-04-04T12:38:42.643

Basic :

CREATE TABLE table1 (
    columnA INTEGER NOT NULL,
    columnB INTEGER NOT NULL,
    PRIMARY KEY (columnA, columnB)
);

If your columns are foreign keys of other tables (common case) :

CREATE TABLE table1 (
    table2_id INTEGER NOT NULL,
    table3_id INTEGER NOT NULL,
    FOREIGN KEY (table2_id) REFERENCES table2(id),
    FOREIGN KEY (table3_id) REFERENCES table3(id),
    PRIMARY KEY (table2_id, table3_id)
);

CREATE TABLE table2 (
    id INTEGER NOT NULL,
    PRIMARY KEY id
);

CREATE TABLE table3 (
    id INTEGER NOT NULL,
    PRIMARY KEY id
);

score 17 · Answer 5 · edited Oct 16 '12 at 23:48

17

Primary key fields should be declared as not null (this is non standard as the definition of a primary key is that it must be unique and not null). But below is a good practice for all multi-column primary keys in any DBMS.

create table foo
(
  fooint integer not null
  ,foobar string not null
  ,fooval real
  ,primary key (fooint, foobar)
)
;

edited Oct 16 '12 at 23:48

Waynn Lue

11,344
8
51
76

answered Oct 16 '12 at 23:41

Ken Reed

171
1
2

True. [SQLite calls out](https://www.sqlite.org/lang_createtable.html#the_primary_key): _According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. [...] SQLite allows NULL values in a PRIMARY KEY column._ Hence it's required to pass `NOT NULL` for all primary key columns if using SQLite. – legends2k Aug 22 '22 at 06:17

score 14 · Answer 6 · answered Nov 02 '15 at 10:55

14

Since version 3.8.2 of SQLite, an alternative to explicit NOT NULL specifications is the "WITHOUT ROWID" specification: [1]

NOT NULL is enforced on every column of the PRIMARY KEY
in a WITHOUT ROWID table.

"WITHOUT ROWID" tables have potential efficiency advantages, so a less verbose alternative to consider is:

CREATE TABLE t (
  c1, 
  c2, 
  c3, 
  PRIMARY KEY (c1, c2)
 ) WITHOUT ROWID;

For example, at the sqlite3 prompt: sqlite> insert into t values(1,null,3); Error: NOT NULL constraint failed: t.c2

answered Nov 02 '15 at 10:55

peak

105,803
17
152
177

1

For anyone reading this nowadays: `WITHOUT ROWID` has additional implications, and it should not be used as an alternative to writing `NOT NULL` next to your primary key. – shadowtalker May 06 '20 at 23:15
I always specify `NOT NULL` for `PRIMARY KEY` irrespective of `WITHOUT ROWID` since it's explict, doesn't change with defaults and save me from remembering quirks of SQLite. [Related comment on Ken Reed's answer](https://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns#comment129693789_12925089). – legends2k Aug 22 '22 at 06:59

score 7 · Answer 7 · edited Dec 28 '20 at 16:40

7

The following code creates a table with 2 column as a primary key in SQLite.

SOLUTION:

CREATE TABLE IF NOT EXISTS users (
    id TEXT NOT NULL, 
    name TEXT NOT NULL, 
    pet_name TEXT, 
    PRIMARY KEY (id, name)
)

edited Dec 28 '20 at 16:40

Slava Rozhnev

9,510
6
23
39

answered Oct 16 '18 at 11:52

Naveen Kumar V

2,559
2
29
43

score 3 · Answer 8 · edited May 23 '17 at 12:34

3

In another way, you can also make the two column primary key unique and the auto-increment key primary. Just like this: https://stackoverflow.com/a/6157337

edited May 23 '17 at 12:34

Community

1
1

answered May 16 '17 at 09:33

ChenYilong

8,543
9
56
84

score 2 · Answer 9 · edited Dec 28 '20 at 16:40

2

PRIMARY KEY (id, name) didn't work for me. Adding a constraint did the job instead.

CREATE TABLE IF NOT EXISTS customer (
    id INTEGER, name TEXT,
    user INTEGER,
    CONSTRAINT PK_CUSTOMER PRIMARY KEY (user, id)
)

edited Dec 28 '20 at 16:40

Slava Rozhnev

9,510
6
23
39

answered Dec 12 '18 at 06:21

Choxmi

1,584
4
29
47

SQLite primary key on multiple columns

9 Answers9

Linked

Related