CODEWITHSUNDEEP
sqlsqlite
ahmet alp balkan
ahmet alp balkan

Reputation: 45312

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

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?

Upvotes: 24

Views: 27530

Answers (4)

Jesse Gador
Jesse Gador

Reputation: 85

Surprisingly, I was able to implement auto-increment for SqLite with composite keys with syntax exactly the same with SQL Server:

Use IDENTITY (1,1)

create table [dbo].[Person]
{
   ID int IDENTITY (1,1) not null,
   CompositeID1 int not null,
   CompositeID2 int not null,

   constraint [pk_person] primary key clustered (ID asc, CompositeID1 asc, CompositeID2 asc)
}

Upvotes: 1

pux
pux

Reputation: 29

You can also write like this:

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

Upvotes: -2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95761

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.

Upvotes: 31

Stephane Gosselin
Stephane Gosselin

Reputation: 9148

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.

Upvotes: 16

Related Questions