I have an existing database with several tables that doesn’t have an explicit primary key. Are there any easy ways to add a primary key to existing tables?
I have tried “alter table add primary key (ID)” where ID is a newly added integer field, but it doesn’t seem to do the trick.
I could do it the hard way, by copying the table, record by record, into a new table, but as some of my tables has more the 1.500.000 records, I expect it to be rather time consuming. Any suggestions?
You need to use CREATE UNIQUE INDEX
Depends on the database engine you’re using as to what will work well
Since primary key also implies not null and no duplicates just adding a new column wont necessarily do it since the default values may overlap when you try to create it
So copying the data out & back in once you have added the column may work well esp since you can make your new primary key autoincrement
Then its a matter of selecting all your existing data out into a temporary table
Altering the table to add the primary key (you may have to drop & recreate it)
Then selecting all the data back into the table
Even on a million rows this should be reasonably quick
‘Easy’ is very relative …
I’d do something like this:
Assume i’d had created a table address without primary key:
CREATE TABLE "address" (lastname TEXT, firstname TEXT);And then rename the address table, create a new table with a primary key ‘id’ and then select the records from the renamed table into the new table. Then drop the old table.
BEGIN TRANSACTION;
ALTER TABLE "address" RENAME TO temp_table;
CREATE TABLE "address" (lastname TEXT, firstname TEXT, id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE);
Copy data back, something like:
INSERT into address (lastname,firstname) SELECT lastname,firstname from temp_table;
DROP TABLE temp_table;
COMMIT;
Thanks 
Creating new tables and using “Insert into…” did the trick. It takes 1-2 minutes to update the entire database - and it only happens once.