Sqlite primary key on multiple columns

Question

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

Answer 1

According to the documentation, it's

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

Answer 2

I don't have karma to edit yet, but the documentation link in the answer above returned 404.

On inspection, the link above is correct, but Stack Overflow replaces the _ with %5F which breaks sqlite.org somehow. The link below appears to work.

http://www.sqlite.org/lang_createtable.html

Answer 3

Yes. But remember that such primary key allows to have 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> 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
NULL|NULL|bla-bla
NULL|NULL|bla-bla