65.9K
CodeProject is changing. Read more.
Home

Auto Increment Column Options in Different Databases

Jul 5, 2022

CPOL
viewsIcon

7302

downloadIcon

27

How to auto increment column options in databases

Background

SQLServer database IDENTITY(1, 1) column will automatically generate and populate a numeric column value each time a new row is inserted into a table. In PostgreSQL, Oracle, and MySql, things are a bit different and there are some alternative ways. Here, we are going to take a quick look at some of the options.

PostgreSQL

SERIAL

This is available from v8:

DROP TABLE IF EXISTS tbl_users;

CREATE TABLE tbl_users (
    id SERIAL,
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (DEFAULT, 'user2');

SELECT * FROM tbl_users;

We can also use SMALLSERIAL, BIGSERIAL.

Name Storage Size Range
SMALLSERIAL 2 bytes 1 to 32,767
SERIAL 4 bytes 1 to 2,147,483,647
BIGSERIAL 8 bytes 1 to 9,223,372,036,854,775,807

SEQUENCE

This is available from v9.1 and similar to Oracle.

DROP TABLE IF EXISTS tbl_users;
DROP SEQUENCE IF EXISTS seq_tbl_users_id;

CREATE SEQUENCE seq_tbl_users_id;
CREATE TABLE tbl_users (
    id INT NOT NULL DEFAULT nextval('seq_tbl_users_id'),
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (nextval('seq_tbl_users_id'), 'user2');

SELECT * FROM tbl_users;

IDENTITY

This is available from v10.

DROP TABLE IF EXISTS tbl_users;

CREATE TABLE tbl_users (
    id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name VARCHAR NULL,
    
    CONSTRAINT pk_tbl_users PRIMARY KEY(id) 
);

INSERT INTO tbl_users(name) VALUES ('user1');
INSERT INTO tbl_users(id, name) VALUES (DEFAULT, 'user2');

SELECT * FROM tbl_users;

Oracle

SEQUENCE

CREATE SEQUENCE SEQ_USER_ID
MINVALUE 1
MAXVALUE 2147483647
START WITH 1
INCREMENT BY 1
CACHE 10;

CREATE TABLE TBL_USER (
    ID INT NOT NULL,
    NAME VARCHAR(255) NULL,
  
    CONSTRAINT PK_TBL_USER PRIMARY KEY (ID)
);

INSERT INTO TBL_USER(ID, NAME) VALUES (SEQ_USER_ID.NEXTVAL, 'user2');

SELECT * FROM TBL_USER;
SELECT SEQ_USER_ID.NEXTVAL AS ID FROM DUAL;
DROP TABLE TBL_USER;
DROP SEQUENCE SEQ_USER_ID;

IDENTITY

This is available from 12c.

CREATE TABLE TBL_USER (
    ID INT GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 1 INCREMENT BY 1,
    NAME VARCHAR(255) NULL,
  
    CONSTRAINT PK_TBL_USER PRIMARY KEY (ID)
);

INSERT INTO TBL_USER(NAME) VALUES ('user1');
INSERT INTO TBL_USER(NAME) VALUES ('user2');

SELECT * FROM TBL_USER;
DROP TABLE TBL_USER;

SQLServer

IDENTITY

DROP TABLE IF EXISTS TblUser;

CREATE TABLE TblUser (
    Id INT IDENTITY(1, 1),
    [Name] VARCHAR(MAX) NULL,
    
    CONSTRAINT PK_TblUser PRIMARY KEY(Id)
);

INSERT INTO TblUser([Name]) VALUES ('user1');

SELECT * FROM TblUser;

MySQL

AUTO_INCREMENT

This is available from v5.6.

DROP TABLE IF EXISTS tbl_user;

CREATE TABLE tbl_user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NULL,
  
    CONSTRAINT PK_tbl_user PRIMARY KEY (id)
);

INSERT INTO tbl_user(NAME) VALUES ('user1');

SELECT * FROM tbl_user;

SQLite

AUTOINCREMENT

This is available from v3.26.

DROP TABLE IF EXISTS tbl_user;

CREATE TABLE tbl_user
(
    Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name VARCHAR NULL
);

INSERT INTO tbl_user(name) VALUES ('user1');

SELECT * FROM tbl_user;

Get Inserted Id

PostgreSQL

INSERT INTO tbl_users(name) VALUES ('user1') RETURNING id;            --postgresql

Oracle

DECLARE                                                               --oracle
    INSERTED_ID INT;
BEGIN
    INSERT INTO TBL_USER(NAME) VALUES ('user1') RETURNING ID INTO INSERTED_ID;
    DBMS_OUTPUT.PUT_LINE(INSERTED_ID);
END;
/

SQLServer

INSERT INTO TblUser([Name]) OUTPUT INSERTED.Id VALUES ('user1');      --sqlserver

MySQL

INSERT INTO tbl_user(NAME) VALUES ('user1');
SELECT LAST_INSERT_ID() AS id;                                        --mysql

SQLite

INSERT INTO tbl_user(name) VALUES ('user1');
SELECT last_insert_rowid() Id;                                        --sqlite

Check Database Version

Here are a few SQL queries to check our working database versions:

SELECT @@VERSION;            /*sql server*/       /*also works with mysql*/
SELECT * FROM V$VERSION;     /*oracle*/
SELECT VERSION();            /*mysql*/            /*also works with postgre*/
SELECT VERSION();            /*postgre sql*/      /*also works with mysql*/

Online Playground

References

PostgreSQL

Oracle

History

  • 5th July, 2022: Initial version