663

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

dreftymac
  • 27,818
  • 25
  • 108
  • 169
Bogdan Gavril MSFT
  • 18,632
  • 10
  • 50
  • 73

9 Answers9

866

According to the documentation, it's

CREATE TABLE something (
  column1, 
  column2, 
  column3, 
  PRIMARY KEY (column1, column2)
);
Brian Campbell
  • 289,867
  • 55
  • 346
  • 327
  • 5
    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
  • 8
    @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
  • 13
    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
167
CREATE TABLE something (
  column1 INTEGER NOT NULL,
  column2 INTEGER NOT NULL,
  value,
  PRIMARY KEY ( column1, column2)
);
Czechnology
  • 14,253
  • 9
  • 58
  • 83
xiwok
  • 1,679
  • 1
  • 10
  • 2
  • Doesn't Primary Key impose a NOT NULL? – pratnala Mar 13 '14 at 18:12
  • 27
    @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
44

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
Deepzz
  • 4,489
  • 1
  • 26
  • 52
jsmarkus
  • 1,354
  • 1
  • 15
  • 18
  • 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
  • 5
    @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
35

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
);
compte14031879
  • 1,341
  • 12
  • 27
15

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)
)
;
Waynn Lue
  • 11,048
  • 7
  • 49
  • 71
Ken Reed
  • 151
  • 1
  • 2
13

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

peak
  • 72,551
  • 14
  • 101
  • 128
  • 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
2

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

Community
  • 1
  • 1
ElonChan
  • 8,372
  • 9
  • 54
  • 83
  • This is what I was look for exactly. Thank you! – Swadhikar Feb 22 '19 at 16:46
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/28889615) – Rob May 03 '21 at 09:24
2

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)
)
Slava Rozhnev
  • 5,534
  • 3
  • 14
  • 27
Naveen Kumar V
  • 1,797
  • 1
  • 19
  • 38
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)
)
Slava Rozhnev
  • 5,534
  • 3
  • 14
  • 27
Choxmi
  • 1,304
  • 3
  • 22
  • 38