Add primary key field to existing Derby table

Question

I'm new to SQL, but managed to import my CSV data into an Apache Derby DB using ij.

My table contains no primary key, and no existing field is suitable, so I'd like to create a new field, generate unique numbers to fill it, and set it as the primary key. How can I do this?

For example, I tried

ALTER TABLE myTable ADD pk AUTO_INCREMENT PRIMARY KEY

but I got a syntax error on AUTO_INCREMENT.

Also, when I google for this sort of thing, I find lots of SQL tips, but few of them seem to apply to Derby. Is there a particular variant of SQL that I should be searching for?

Answer 1

This has been logged as DERBY-3888 in Derby's issue tracker (Jira). Commenting and/or voting for this issue might increase the chance of it being fixed in the next release...

Answer 2

I had the same problem, and as far as I can tell, there is no way to create a new primary key to an existing Apache Derby database. Derby does not allow you to add a column with GENERATED as an attribute.

Here's the method I found has worked the best.

---

Based on the original table:

CREATE TABLE contacts (firstname varchar(50), lastname varchar(50), 
address varchar(50), phone1 varchar(14), phone2 varchar(14) );

Which looks like:

firstname     lastname           address        phone1          phone2
rich          bush               27 oak hill dr 11932035551234  11932035551234

Create a new table with the same structure, in addition to having the primary key.

     CREATE TABLE contactsTemp 
 (primary_key INT not null GENERATED ALWAYS as identity, 
 firstname varchar(50), lastname varchar(50), 
 address varchar(50), phone1 varchar(14), phone2 varchar(14)) 

Breaking down the primary_key field. The first two are standard SQL keywords. The key will be an integer, and can't be null. Apache DB uses the keyword generated as their AUTO_GENERATED. There's two keywords that can follow generated ALWAYS and AS DEFAULT. Always means that it can't be manually entered, AS DEFAULT means it will be auto, unless you specify a number. Per the Apache spec

Copy data into new table via a INSERT

insert into contactsTemp (firstname, lastname, address , phone1 , phone2 ) 
SELECT firstname, lastname, address , phone1 , phone2 from contacts

Now remove the original contacts table

DROP TABLE contacts

Rename contactsTemp to contacts

RENAME TABLE contactstemp TO contacts