Auto-incremented fields in apache’s derby

Unlike MySQL and other SQL dialects, apache derby doesn’t support the AUTO_INCREMENT keyword. To create an auto incremented filed in derby, you can use

CREATE TABLE customer
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
name VARCHAR(40) NOT NULL,
address VARCHAR(1024),
city VARCHAR(30),
CONSTRAINT primary_key PRIMARY KEY (id)
) ;

The value of id is now incremented automagically on every insert. You don’t have to specify it manually in the insert command

INSERT INTO customer(name , address, city)
   VALUES('Mr. Peanutbutter', '221 W 31st St', New York City);

For instruction on how to create auto incremented fields in other SQL dialects, have a look at the SQL Dialects Reference.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.