What is the syntax for specifying a primary key on more than 1 column in SQLite ?
-
24This is also called compound key en.wikipedia.org/wiki/Compound_keyOneWorld– OneWorld2013-01-07 16:36:52 +00:00Commented Jan 7, 2013 at 16:36
-
15@OneWorld Or composite key, if any of the columns isn't a key itself.Michael– Michael2014-04-09 16:47:37 +00:00Commented Apr 9, 2014 at 16:47
Add a comment
|
9 Answers
According to the documentation on CREATE TABLE, specifically table-constraint, it's:
CREATE TABLE something (
column1,
column2,
column3,
PRIMARY KEY (column1, column2)
);
4 Comments
Yar
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.
Brian Campbell
@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.
vovahost
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.
U. Windl
@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.
CREATE TABLE something (
column1 INTEGER NOT NULL,
column2 INTEGER NOT NULL,
value,
PRIMARY KEY ( column1, column2)
);
2 Comments
pratnala
Doesn't Primary Key impose a NOT NULL?
Brian Campbell
@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.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
4 Comments
Pastafarianist
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?
The incredible Jan
@Pastafarianist 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."
holdenweb
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.
U. Windl
Actually this is not an answer, but a response to a comment on stackoverflow.com/a/3685672/6607497 which is a duplicate of stackoverflow.com/a/734704/6607497. Probably too late for the administrators now.
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
);
Comments
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)
)
;
1 Comment
legends2k
True. SQLite calls out: 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.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
2 Comments
shadowtalker
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.legends2k
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.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)
)
Comments
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
Comments
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)
)
