oracle增加字段为主键自增_Oracle创建自增字段

Oracle在创建表时和其他的数据库有点不一样，MySQL中可以使用“auto_increment”即可。但是oracle12c以前有点麻烦，需要使用序列和触发器达到目的。从12c开始，可以使用新特性Identity Columns实现自增长列功能，也可以使用序列的NEXTVAL and CURRVAL的值作为列的默认值，来实现列自增。

1、11g序列加触发器为表增加自增字段

--表增加列

alter table DW_SUNACPARK_EQUI_FAULT_MANU add auto_increment_no number(12) ;

-- Add comments to the columns

comment on column DW_SUNACPARK_EQUI_FAULT_MANU.auto_increment_no

is '触发器自增ID';

--更改列值

Update DW_SUNACPARK_EQUI_FAULT_MANU set auto_increment_no=rownum;

commit;

select max(auto_increment_no) from DW_SUNACPARK_EQUI_FAULT_MANU;

alter table DW_SUNACPARK_EQUI_FAULT_MANU modify auto_increment_no not null ;

--创建序列 start根据上边表中数据最大值

create sequence SEQ_DW_SPARK_EQUI_FAULT_MANU

minvalue 1

nomaxvalue

start with 29

increment by 1

cache 20;

--建立触发器CREATE OR REPLACE TRIGGER TRI_DW_SPARK_EQUI_FAULT_MANU

BEFORE INSERT

ON DW_SUNACPARK_EQUI_FAULT_MANU

FOR EACH ROW

WHEN (new.auto_increment_no is null)

DECLARE

v_no DW_SUNACPARK_EQUI_FAULT_MANU.auto_increment_no%TYPE;

BEGIN

SELECT SEQ_DW_SPARK_EQUI_FAULT_MANU.nextval INTO v_no FROM DUAL;

:new.auto_increment_no := v_no;

END TRI_DW_SPARK_EQUI_FAULT_MANU;

2、12c使用序列的NEXTVAL and CURRVAL的值作为默认值

(1)默认值可为空列

--创建序列

create sequence seq_test

minvalue 1

nomaxvalue

start with 1

increment by 1

cache 10;

--建表

CREATE TABLE test (

id NUMBER DEFAULT seq_test.NEXTVAL,

name VARCHAR2(20)

);

--插入数据

INSERT INTO test (name) VALUES ('only name');

INSERT INTO test (id, name) VALUES (666, 'id=666 and name');

INSERT INTO test (id, name) VALUES (NULL, 'id=NULL and name');

commit;

--查询数据

select * from test;

id name

------   ------

1 only name

666 id=666 and name

(Null)   id=NULL and name

(2)默认值非空列

--创建序列

create sequence seq_nonull_test

minvalue 1

nomaxvalue

start with 1

increment by 1

cache 10;

--建表

CREATE TABLE test2 (

id1 NUMBER DEFAULT seq_test.NEXTVAL,

id2 NUMBER DEFAULT seq_nonull_test.NEXTVAL,

name VARCHAR2(20)

);

--插入数据

INSERT INTO test2 (name) VALUES ('only name');

INSERT INTO test2 (id1,id2, name) VALUES (666,666, 'id1=666 id2=666 and name');

INSERT INTO test2 (id1,id2,name) VALUES (NULL,NULL, 'id1=NULL id2=NULL and name');

commit;

--查询数据

select * from test;

id1 id2 name

------  ------  ------

1 1 only name

666 666 id1=666 id2=666 and name

(Null)   2       id1=NULL id2=NULL and name

查询数据，可以看到id2为NULL时候，被默认转换使用了seq_nonull_test.NEXTVAL。

注：

采用此种方法，一定要注意不要自行插入列值，避免与sequence生成的出现冲突。12c以后，最好使用新特性Identity Columns。

3、12C 新特性Identity Columns

(1)GENERATED ALWAYS AS IDENTITY方式测试

--创建表

create table test3 (id number GENERATED ALWAYS AS IDENTITY,

name varchar2(30));

--查询对象

select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

--------------- -----------------------

ISEQ$$_15264 SEQUENCE

TEST3 TABLE

--查询创建表DDL语句

select dbms_metadata.get_ddl('TABLE','TEST3') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','TEST3')

--------------------------------------------------------------------------------

CREATE TABLE "TEST"."TEST3"

( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE

9999999999999999999999999999 INCREMENT BY 1 START WITH 1

CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE,

"NAME" VARCHAR2(100)

) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

TABLESPACE "TEST"

--插入、更改、删除数据

INSERT INTO TEST3 VALUES(1,'DBA');

INSERT INTO TEST3 VALUES(1,'DBA')

*

第 1 行出现错误:

ORA-32795: 无法插入到“始终生成”身份列

INSERT INTO TEST3 (name) VALUES('DBA');

已创建 1 行。

INSERT INTO TEST3 (name) VALUES('SYSDBA');

已创建 1 行。

commit;

提交完成。

select * from test3;

ID NAME

---------- ------------------------------

1 DBA

2 SYSDBA

update test3 set id=3 where id=2;

update test3 set id=3 where id=2

*

第 1 行出现错误:

ORA-32796: 无法更新“始终生成”身份列

delete from test3 where id=1;

已删除 1 行。

commit;

提交完成。

select ISEQ$$_15264.nextval from dual;

NEXTVAL

----------

3

INSERT INTO TEST3 (name) VALUES('SYSTEM');

已创建 1 行。

commit;

提交完成。

select * from test;

ID NAME

---------- ------------------------------

2 SYSDBA

4 SYSTEM

--删除序列

drop sequence ISEQ$$_15264;

drop sequence ISEQ$$_15264

*

第 1 行出现错误:

ORA-32794: 无法删除系统生成的序列

--删除表

drop table test3;

表已删除。

select object_name,object_type from user_objects where object_name=' ISEQ$$_15264';

OBJECT_NAME OBJECT_TYPE

-------------------- -----------------------

ISEQ$$_15264 SEQUENCE

purge table test3;

表已清除。

select object_name,object_type from user_objects where object_name=

'ISEQ$$_15264';

未选定行

结论：

GENERATED ALWAYS AS IDENTITY 列无法人工指定值和修改该值；

GENERATED IDENTITY 本质也是通过SEQUENCE实现；

GENERATED IDENTITY 中SEQUENCE不能单独被删除；

GENERATED IDENTITY 中的表删除,如果存在回收站中,该SEQUENCE依然存储,如果表被彻底删除,则SEQUENCE也被删除；

GENERATED IDENTITY 中的SEQUENCE可以通过SELECT语句查询；

通过ALTER TABLE语句来修改GENERATED IDENTITY 的SEQUENCE相关值。

(2)GENERATED BY DEFAULT AS IDENTITY方式测试

create table test4 (id number GENERATED BY DEFAULT AS IDENTITY,name varchar2(100)) tablespace test;

表已创建。

insert into test4 values (1,'DBA');

已创建 1 行。

insert into test4(name) values ('SYSDBA');

已创建 1 行。

col name for a20

select * from test4;

ID NAME

---------- --------------------

1 DBA

1 SYSDBA

insert into test4 values (null,'DBA');

insert into test4 values (null,'DBA')

*

第 1 行出现错误:

ORA-01400: 无法将 NULL 插入 ("TEST"."test4"."ID")

desc test4

名称 是否为空? 类型

----------------------------------------- -------- ---------------------------

ID NOT NULL NUMBER

NAME VARCHAR2(100)

结论：

GENERATED BY DEFAULT AS IDENTITY方式不能在该列中插入null值；

GENERATED BY DEFAULT AS IDENTITY方式可以指定具体值插入。

(3)GENERATED BY DEFAULT ON NULL AS IDENTITY方式测试

create table test5 (id number GENERATED BY DEFAULT on null AS IDENTITY,name varchar2(100)) tablespace test;

表已创建。

desc test5

名称 是否为空? 类型

----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER

NAME                                               VARCHAR2(100)

insert into test5 values (null,'DBA');

已创建 1 行。

select * from test5;

ID NAME

---------- --------------------

1 DBA

结论：

GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查询null值，只是默认转换为对应的sequence值。

4、MySQL自增列

--Mysql 中每张表只能有一个自增列，并且自增列必须是主键或者唯一键。

root@localhost [test]> create table test6(id int not null auto_increment primary key);

Query OK, 0 rows affected (0.01 sec)

-插入空值，是允许的，默认会用自增列值插入。

root@localhost [test]>insert into test6 values();

Query OK, 1 row affected (0.01 sec)

root@localhost [test]>select * from test6;

+----+

| id |

+----+

| 1 |

+----+

1 row in set (0.00 sec)

--允许插入的值不连续

root@localhost [test]>insert into test6 values(3);

Query OK, 1 row affected (0.00 sec)

root@localhost [test]>select * from test6;

+----+

| id |

+----+

| 1 |

| 3 |

+----+

2 rows in set (0.00 sec)

--表加一列，显式插入新增列，自增列会隐式地从当前最大值自增。

root@localhost [test]>alter table test6 add b int;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

root@localhost [test]>insert into test6(b) values(4);

Query OK, 1 row affected (0.00 sec)

root@localhost [test]>select * from test6;

+----+------+

| id | b |

+----+------+

| 1 | NULL |

| 3 | NULL |

| 4 | 4 |

+----+------+

3 rows in set (0.00 sec)