Set multple primary keys with mixed syntax

user_10211997 0
Viewed 30

I am currently struggling with multiple primary keys with a forced mixed syntax. The script i am writing is supposed to duplicate a remote database but i need to add another primary key to the table.

The new key must be a "INTEGER AUTOINCREMENT"-Key.

The "CREATE TABLE"-Statement from the remote database looks currently like this:

CREATE TABLE "Table_1" (id text, ord text, mod_date TEXT, val TEXT, PRIMARY KEY (id,ord));

What i tried and failed with is following modification:

CREATE TABLE "Table_1" (_id INTEGER PRIMARY KEY AUTOINCREMENT, id text, ord text, mod_date TEXT, val TEXT, PRIMARY KEY (id,ord));

Is there a way to achieve this or do i have to write a function to unzip the "PRIMARY KEY(...)"-Part to be able to set the "PRIMARY KEY"-Statement for each primary key?

sqlite python
1 Answers

You can't have 2 primary keys in a table.
You have defined the primary key as PRIMARY KEY (id,ord).

But SQLite does provide what you need because all tables that are not created with WITHOUT ROWID are Rowid tables.
So you already have an auto increment column in your table named rowid and you can use it although it is not defined in the CREATE statement of the table.
This is actually:

The true primary key for a rowid table (the value that is used as the key to look up rows in the underlying B-tree storage engine)

user_104988280
Related