{"id":337,"date":"2024-08-26T06:03:55","date_gmt":"2024-08-26T06:03:55","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=337"},"modified":"2025-05-17T05:13:00","modified_gmt":"2025-05-17T05:13:00","slug":"sql-auto-increment","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/sql-auto-increment\/","title":{"rendered":"SQL Auto Increment"},"content":{"rendered":"\n

Auto-increment allows a unique number to be generated automatically when a new record is inserted. This is commonly used for primary keys \u2014 ensuring each record gets a unique identifier without manual input.<\/p>\n\n\n\n

<\/p>\n\n\n\n

SQL Auto Increment Syntax<\/h2>\n\n\n\n

In SQLite, you declare a column as INTEGER PRIMARY KEY AUTOINCREMENT. In our playground, AUTOINCREMENT works only with the column declared as INTEGER PRIMARY KEY.<\/p>\n\n\n\n

CREATE TABLE Example (\n  id INTEGER PRIMARY KEY AUTOINCREMENT,\n  name TEXT NOT NULL\n);<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Auto Increment Syntax for MySQL<\/h3>\n\n\n\n
CREATE TABLE Persons (\n  Personid INT NOT NULL AUTO_INCREMENT,\n  LastName VARCHAR(255) NOT NULL,\n  FirstName VARCHAR(255),\n  Age INT,\n  PRIMARY KEY (Personid)\n);<\/pre><\/div>\n\n\n\n
\n
Note:<\/strong> This syntax does not work in SQLite<\/strong> \u2014 AUTO_INCREMENT<\/code> is specific to MySQL.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL Auto Increment Syntax for SQL Server<\/h3>\n\n\n\n
CREATE TABLE Persons (\n  Personid INT IDENTITY(1,1) PRIMARY KEY,\n  LastName VARCHAR(255) NOT NULL,\n  FirstName VARCHAR(255),\n  Age INT\n);<\/pre><\/div>\n\n\n\n
    \n
  • IDENTITY(1,1)<\/code> means: start at 1 and increment by 1.<\/li>\n<\/ul>\n\n\n\n
    \n
    Note:<\/strong> IDENTITY<\/code> is not valid in SQLite.<\/p>\n<\/blockquote>\n\n\n\n
    <\/p>\n\n\n\n
    SQL Auto Increment Syntax for Access<\/h3>\n\n\n\n
    CREATE TABLE Persons (\n  Personid AUTOINCREMENT PRIMARY KEY,\n  LastName VARCHAR(255) NOT NULL,\n  FirstName VARCHAR(255),\n  Age INT\n);<\/pre><\/div>\n\n\n\n
    \n
    Note: AUTOINCREMENT<\/code> is specific to Microsoft Access and not supported in SQLite<\/strong>.<\/p>\n<\/blockquote>\n\n\n\n
    <\/p>\n\n\n\n
    SQL Auto Increment Syntax for Oracle<\/h3>\n\n\n\n
    In Oracle, auto-increment is achieved using a SEQUENCE<\/code>:<\/p>\n\n\n\n
    CREATE SEQUENCE seq_person\nMINVALUE 1\nSTART WITH 1\nINCREMENT BY 1\nCACHE 10;<\/pre><\/div>\n\n\n\n
    Then use the sequence when inserting:<\/p>\n\n\n\n
    INSERT INTO Persons (Personid, FirstName, LastName)\nVALUES (seq_person.NEXTVAL, 'Lars', 'Monsen');<\/pre><\/div>\n\n\n\n
    \n
    Note: Oracle sequences are not supported<\/strong> in SQLite.<\/p>\n<\/blockquote>\n\n\n\n
    <\/p>\n\n\n\n
    SQL Auto Increment Example<\/h2>\n\n\n\n
    This query creates a table using SQLite\u2019s auto-increment format:<\/p>\n\n\n\n
    CREATE TABLE TestPatients (\n  id INTEGER PRIMARY KEY AUTOINCREMENT,\n  name TEXT NOT NULL\n);<\/pre><\/div>\n\n\n\n
    To insert a new patient:<\/p>\n\n\n\n
    INSERT INTO TestPatients (name)\nVALUES ('Maria Santos');<\/pre><\/div>\n\n\n\n
    This query will insert Maria Santos and assign a unique id automatically.<\/p>\n\n\n\n
    You can confirm with:<\/p>\n\n\n\n
    SELECT * FROM TestPatients;<\/pre><\/div>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    SQL Auto Increment Labs<\/h2>\n\n\n\n