Sqlite primary key on multiple columns

Question

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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

User · Answer

According to the documentation  it s  CREATE TABLE something     column1     column2     column3     PRIMARY KEY  column1  column2

User · Answer

Yes  But remember that such primary key allow NULL values in both columns multiple times   Create a table as such       sqlite gt  CREATE TABLE something   column1  column2  value  PRIMARY KEY  column1  column2      Now this works without any warning   sqlite gt  insert into something  value  VALUES   bla-bla    sqlite gt  insert into something  value  VALUES   bla-bla    sqlite gt  select   from something  NULL NULL bla-bla NULL NULL bla-bla

User · Answer

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 gt  insert into t values 1 null 3     Error  NOT NULL constraint failed  t c2

User · Answer

CREATE TABLE something     column1 INTEGER NOT NULL    column2 INTEGER NOT NULL    value    PRIMARY KEY   column1  column2

User · Answer

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

[sqlite] Sqlite primary key on multiple columns

Examples related to sqlite

Examples related to primary-key

Examples related to ddl

Examples related to composite-primary-key