慕森王
Oracle中没有“自动增量”或“标识”列截至甲骨文11g。但是,您可以使用序列和触发器轻松地对其建模:表定义:CREATE TABLE departments (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);ALTER TABLE departments ADD (
CONSTRAINT dept_pk PRIMARY KEY (ID));CREATE SEQUENCE dept_seq START WITH 1;触发定义:CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROWBEGIN
SELECT dept_seq.NEXTVAL INTO :new.id FROM dual;END;/最新情况:IDENTITY列现在可在Oracle 12c上使用:create table t1 (
c1 NUMBER GENERATED by default on null as IDENTITY,
c2 VARCHAR2(10)
);或指定起始值和增量值,同时防止对标识列进行任何插入(GENERATED ALWAYS)(同样,Oracle 12c+仅限于)create table t1 (
c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
c2 VARCHAR2(10)
);另外,Oracle 12还允许将序列用作默认值:CREATE SEQUENCE dept_seq START WITH 1;CREATE TABLE departments (
ID NUMBER(10) DEFAULT dept_seq.nextval NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);ALTER TABLE departments ADD (
CONSTRAINT dept_pk PRIMARY KEY (ID));