Wednesday, June 18, 2008

Apache Derby (JavaDB) INSERT Statement with IDENTITY

If you are trying to insert values into a table which has an identity column, the documentation is not very clear. If I create a table in an Apache Derby (JavaDB) database which has a primary key which is generated always, the syntax for doing a query based insert is a little different.

Here is the table:
CREATE TABLE FAMILY (
ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
DESCRIPTION VARCHAR(50) NOT NULL
)
Now if I want to insert values from another table here is the query I used:
INSERT INTO FAMILY (DESCRIPTION) ( SELECT DISTINCT FAMILY FROM EQUIPMENT_GROUP WHERE FAMILY IS NOT NULL)
Please note that I did not put anything into the ID column. If I try to specify a value, it will result in an exception
Exception: Attempt to modify an identity column 'ID'.
Since the column has been marked as GENERATED ALWAYS, it handles incrementing the key and assigning it.

0 comments :

Popular Posts