Oracle主键自增

1、建表

CREATE TABLE TEST_TABLE
(
  ID INT NOT NULL,
  NAME VARCHAR2(40) NULL,
  AGE VARCHAR2(40) NULL
);

Oracle主键自增_建表

2、设置主键

ALTER TABLE TEST_TABLE ADD CONSTRAINT TEST_TABLE_PK PRIMARY KEY (ID);

Oracle主键自增_主键_02

3、新建序列

CREATE SEQUENCE TEST_TABLE_AUTOINC_SEQ
MINVALUE 1
NOMAXVALUE
INCREMENT BY 1
START WITH 1
NOCACHE;

Oracle主键自增_oracle_03

4、新建触发器

CREATE OR REPLACE TRIGGER  TEST_TABLE_AUTOINC_TG
BEFORE INSERT ON TEST_TABLE FOR EACH ROW 
BEGIN
  SELECT TEST_TABLE_AUTOINC_SEQ.NEXTVAL INTO:NEW.ID FROM DUAL;
END;

Oracle主键自增_建表_04

5、测试

INSERT INTO TEST_TABLE (NAME,AGE)VALUES('测试1','18');
INSERT INTO TEST_TABLE (NAME,AGE)VALUES('测试2','19');
INSERT INTO TEST_TABLE (NAME,AGE)VALUES('测试3','20');

Oracle主键自增_oracle_05

6、结果

SELECT * FROM TEST_TABLE;

Oracle主键自增_oracle_06