Sqlite Multi-Primary Key on a Table, One of Them Is Auto Increment

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

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.

Use of composite keys with an AUTOINCREMENT column in SQLite database

No, the column does not need to be a primary key. However, SQLite allows AUTOINCREMENT only if one key is set as primary key. Here multiple columns are set as Primary Key. Hence auto increment on one of them does not work.

Refer to the answer to this stackoverflow post :

Auto Increment on Composite Primary Key

SQLite: double primary key and autoincrement

You can't autoincrement if you have the same number twice or more often in your table. I know what you want to do but it cant be done with autoincrement.

But you could something like

create table t (x text not null, y integer not null) primary key (x, y);

Also see this: sqlite: multi-column primary key with an auto increment column

Autoincrement a composite key in SQLite

There is no automatic way to do this, like an autoincrement column.

You can insert each row with a SELECT statement that counts the rows of the table with the same orderNumber:

INSERT INTO tablename(orderNumber, lineNumber, SKU, ....)
SELECT 'abc123', COALESCE(MAX(lineNumber), 0) + 1, 'xyz', .... 
FROM tablename 
WHERE orderNumber = 'abc123';

See the demo.

Auto Increment Composite Primary Key

You shouldn't have the ProjectRef column at all. This violates basic rules of database normalization. If you want your front end to display the ProjectRef then just calculate it from the columns that you have.

Auto Increment on Composite Primary Key - Sqlite3 + Python

In sqlite, you only get autoincrement behavior when only one integer column is the primary key. composite keys prevent autoincrement from taking effect.

You can get a similar result by defining id as the only primary key, but then adding an additional unique constraint on id, col3.

If that's still not quite what you want (say, id's don't need to be unique at all), you probably will have to use a trigger to make autoincrement work.

Error when creating a table in SQLite database with composite primary key at AUTOINCREMENT

To get the value of any column autoincrement you need to write it or declare it as primary key.

In SQLite a column declared INTEGER PRIMARY KEY will autoincrement.

EDITED:

As you can not define more than one PRIMARY KEY in a table you have to make the bookdir,lastaddress columns as UNIQUE and define the lid columns as PRIMARY KEY as below:

Try out as below:

 final String createtabBook="CREATE TABLE IF NOT EXISTS BookMark(lid INTEGER PRIMARY KEY AUTOINCREMENT,  
  bookdir TEXT , lastaddress TEXT,addresname TEXT, UNIQUE(bookdir,lastaddress));";

Also add "," after the column addresname TEXT, in your query.

---

Related Topics