From Wikibooks, the open-content textbooks collection
[edit] Auto-increment column
- DB2: Identity columns or sequences combined with triggers (comparison of both techniques)
hh
CREATE TABLE table_name ( column_name INT GENERATED ALWAYS AS IDENTITY )
or
CREATE SEQUENCE sequence_name;
CREATE TABLE table_name ( column_name INT );
CREATE TRIGGER insert_trigger
NO CASCADE BEFORE INSERT ON table_name
REFERENCING NEW AS n
FOR EACH ROW
SET n.column_name = NEXTVAL FOR sequence_name;
CREATE table (column INTEGER NOT NULL PRIMARY KEY);;
CREATE GENERATOR table_gen;;
SET GENERATOR table_gen TO 0;;
CREATE TRIGGER t_column_gen FOR table
BEFORE INSERT
AS
BEGIN
NEW.column = GEN_ID(table_gen, 1);
END;;
- MySQL:
CREATE TABLE table (column INT NOT NULL PRIMARY KEY AUTO_INCREMENT)
- MSSQL:
CREATE TABLE table (column INT IDENTITY(1,1))
- Oracle:
CREATE TABLE table (column NUMBER PRIMARY KEY);
CREATE SEQUENCE sequence START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trigger BEFORE INSERT ON table REFERENCING NEW AS NEW FOR EACH ROW
BEGIN
SELECT sequence.nextval INTO :NEW.column FROM dual;
END;
IDENTITY (start with 1, increment by 1);
CREATE TABLE table (column INTEGER IDENTITY);
or
CREATE TABLE table (column INTEGER IDENTITY (start with 1));
CREATE TABLE table (column INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY)
- Linter- AUTOINC columns (maybe with RANGEs) or sequences combined with triggers
CREATE TABLE table (column SMALLINT AUTOINC);
CREATE TABLE table (column INTEGER AUTOINC);
CREATE TABLE table (column BIGINT AUTOINC);