Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 886
ahmet alp balkan Avatar asked May 27 '11 15:05

ahmet alp balkan


People also ask

Does primary key auto increment SQLite?

In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that's inserted into the table. There are a couple of ways you can create an AUTOINCREMENT column: You can create it implicitly when you define the column as INTEGER PRIMARY KEY .

Are primary keys auto increment?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Can SQLite have multiple primary keys?

Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields.

Can we use auto increment without primary key?

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. So you can indeed have an AUTO_INCREMENT column in a table that is not the primary key.


2 Answers

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.

like image 199
Mike Sherrill 'Cat Recall' Avatar answered Oct 15 '22 08:10

Mike Sherrill 'Cat Recall'


No, I don't think this is possible.

You can create a UNIQUE INDEX which has essentially the same effect as a PRIMARY KEY:

CREATE UNIQUE INDEX pk_index ON "table1"("field1","field2");

Besides, I fail to see the logic of your schema, that is -> if a column is autoincrement and you don't intend to mess with the values manually, it's going to be unique anyway, so it makes a good simple short primary key. Why the composite? You may have good reasons to make another index on the combination of columns, though.

like image 42
stefgosselin Avatar answered Oct 15 '22 09:10

stefgosselin