Friday, March 13, 2009

Oracle : create table with auto increment primary key

How you can create auto increment primary key in Oracle? Answer is you will need sequence and trigger. Example:
CREATE TABLE RULE(
  ID NUMBER(10) NOT NULL,
  WORKFLOW NVARCHAR2(255),
  STATE NVARCHAR2(255) NOT NULL,
  OPERATION NVARCHAR2(255) NOT NULL,
  
  CONSTRAINT RULE_PK PRIMARY KEY (ID),
)
/

CREATE SEQUENCE RULE_SEQ
START WITH 1 
INCREMENT BY 1
NOCACHE;
/

CREATE OR REPLACE TRIGGER RULE_SEQ_TRI
  BEFORE INSERT ON RULE
  FOR EACH ROW
BEGIN
  SELECT RULE_SEQ.NEXTVAL
  INTO   :NEW.ID
  FROM   DUAL;
END RULE_SEQ_TRI;
/