24

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?

ahmet alp balkan
  • 42,679
  • 38
  • 138
  • 214

4 Answers4

31

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
15

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stephane Gosselin
  • 9,030
  • 5
  • 42
  • 65
  • Let's say I have one primary key on a table, should I generate an index for this primary key or is it generated internally? – ahmet alp balkan May 27 '11 at 16:12
  • Yes, the key is automatically an index. See this [short text](http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Basic_concepts4.html) on why this is so. – Stephane Gosselin May 27 '11 at 16:17
  • +1 for the bit on autoincrement. Indeed, if it's autoincrement, it doesn't need to have another field tagging along in the primary key. – MPelletier May 27 '11 at 20:28
  • 2
    If you want to (manually) replicate the database, then the autoincrement column won't necessarily be unique between copies of the database. If you add a second column to the row indicating the unique id of the server it was generated on, this resolves such a conflict. – Michael Apr 09 '14 at 16:52
  • @Michael - I agree, I see composite keys being useful for replication scenarios. I remember having to tackle such an issue with Drupal 6 a few years back. A properly engineered solution could have been a composite key consisting of insertion_id + server_id. Interesting, thanks for bringing this up! – Stephane Gosselin Apr 10 '14 at 04:06
  • IMHO, a PK that is a surrogate-key....and using a (different) unique constraint is the best option. You don't have to "copy" multiple columns for this PK to be a FK in another table. And it is easier to refactor (in the future) a unique constraint over trying to fix a multi column PK. Upvote for this answer. – granadaCoder Jul 16 '20 at 23:13
1

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)
}
Jesse Gador
  • 77
  • 7
-2

You can also write like this:

CREATE TABLE ticket (
     id INTEGER PRIMARY,
     seat TEXT, payment INTEGER,
     PRIMARY KEY (id, seat))
Echilon
  • 10,064
  • 33
  • 131
  • 217
pux
  • 29
  • 2