Derby: Create auto-increment Column 创建自增Column

Categories: Database; Tagged with: Derby; @ September 16th, 2010 16:36

Similar to Microsoft SQL Server, Derby supports IDENTITY columns to help you creating auto-incremented sequence values for primary key columns. Derby offers two variations of IDENTITY columns:

“GENERATED ALWAYS AS IDENTITY” – Derby always provides auto-incremented sequence values to this column. You are not allowed to specify your own values. 由Derby负责提供自增的主键值, 该值不允许插入.
“GENERATED BY DEFAULT AS IDENTITY” – Derby provides auto-incremented sequence values to this as default only when you are not providing values. 仅在未提供主键值时, 由Derby负责插入.
相比BY ALWARYS, BY DEFAULT可Insert主键值.

我们将通过上述方法增加自增Column.

1. 新创建 Create auto-increment Column

CREATE TABLE EMPLOYEE (
employee_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1),
name CHAR(90) NOT NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (employee_ID))

2. 不支持通过Alter新增自增列 Cannot Add auto-increment Column through ALTER TABLE statement

因为Derby不支持通过Alert创建IDENTITY列,
Error: ALTER TABLE statement cannot add an IDENTITY column to a table.

目前在下尚未发现除了重建table之外的有效方法.

参考链接:

1：Tables with Primary Key Column “GENERATED … AS IDENTITY”
2. Create AutoIncrement column/field in Apache Derby
3. Derby PK auto-increment woes

Derby : Create/Rename/Drop Index <-> MySql: 使用数据库文件恢复