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

ID : 274372

viewed : 28

Tags : sqlsqlitesql





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

vote vote

96

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.

vote vote

84

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.

vote vote

72

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) } 
vote vote

70

You can also write like this:

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

54

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







Related QUESTION?