Click here to Skip to main content
15,867,488 members
Articles / Security / Identity
Tip/Trick

Auto Increment Column Options in Different Databases

Rate me:
Please Sign up or sign in to vote.
3.00/5 (1 vote)
4 Jul 2022CPOL 6.4K   26   3   4
How to auto increment column options in databases
This post discusses auto incrementing columns in PostgreSQL, Oracle, and MySql.

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:

SQL
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.

SQL
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.

SQL
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

SQL
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;
SQL
DROP TABLE TBL_USER;
DROP SEQUENCE SEQ_USER_ID;

IDENTITY

This is available from 12c.

SQL
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;
SQL
DROP TABLE TBL_USER;

SQLServer

IDENTITY

SQL
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.

SQL
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.

SQL
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

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

Oracle

SQL
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

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

MySQL

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

SQLite

SQL
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:

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionOracle option missing Pin
Andreas Weiden11-Jul-22 10:19
Andreas Weiden11-Jul-22 10:19 
AnswerRe: Oracle option missing Pin
DiponRoy11-Jul-22 19:17
mvaDiponRoy11-Jul-22 19:17 
Thank you! tested and added that in the post.
QuestionSQL Pin
vahyes6-Jul-22 10:15
vahyes6-Jul-22 10:15 
AnswerRe: SQL Pin
DiponRoy6-Jul-22 18:14
mvaDiponRoy6-Jul-22 18:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.