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?
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 .
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With