sql - SQLite multi-Primary Key on a Table, one of them is Auto Increment

Question

I have multiple (composite) primary keys on a table and one of them will be auto increment. However, interestingly SQLite allows usage of AUTOINCREMENT keyword just after an obligatory PRIMARY KEY keyword.

My query is:

CREATE TABLE ticket (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     seat TEXT, payment INTEGER,
     PRIMARY KEY (id, seat))

However the error is table "ticket" has more than one primary key.

Actually I can avoid other primary keys for this table. But I am coding an ORM framework (hell yeah I'm crazy) and do not want to change structure of PRIMARY KEY constraint generation for a table (because it is allowed in MySQL afaik).

Any solutions to this?

Answer 1

UNIQUE INDEX alone doesn't have the same effect as PRIMARY KEY. A unique index will allow a NULL; a primary key constraint won't. You're better off declaring both those constraints.

CREATE TABLE ticket (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     seat TEXT NOT NULL, 
     payment INTEGER,
     UNIQUE (id, seat));

You should also think hard about whether you really need to accept NULL payments.