{"id":1804,"date":"2018-09-24T07:19:18","date_gmt":"2018-09-24T14:19:18","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1804"},"modified":"2025-01-19T02:21:34","modified_gmt":"2025-01-19T09:21:34","slug":"sql-auto-increment","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-auto-increment\/","title":{"rendered":"SQL Auto Increment"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL auto increment to define a column whose values are sequential integers which are automatically generated.<\/p>\n\n\n\n

When designing a table, we often use the surrogate primary key<\/a> whose values are sequential integers generated automatically by the database system.<\/p>\n\n\n\n

This primary key column is known as an identity<\/a> or auto increment column.<\/p>\n\n\n\n

When a new row is inserted into the auto-increment column, an auto-generated sequential integer is used for the insert.<\/p>\n\n\n\n

For example, if the value of the first row is 1, then the value of the second row is 2, and so on.<\/p>\n\n\n\n

Some databases systems allow you to define the starting and increment values. For example, when the starting value is 10 and the increment value is 20, the sequential integers are 10, 20, 30 and so on.<\/p>\n\n\n\n

Different database systems implement the auto increment column differently. Let’s see how the SQL auto increment column is implemented in some major database systems.<\/p>\n\n\n\n

SQL auto increment column in MySQL #<\/a><\/h2>\n\n\n\n

MySQL uses AUTO_INCREMENT<\/a><\/code> property to define an auto-increment column. See the following example:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> leave_requests (\n    request_id INT<\/span> AUTO_INCREMENT,\n    employee_id INT<\/span> NOT<\/span> NULL<\/span>,\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    end_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    leave_type INT<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(request_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we created a new table named leave_requests<\/code> with the request_id<\/code> is the auto increment column.<\/p>\n\n\n\n
SQL auto increment column in Oracle #<\/a><\/h2>\n\n\n\n
Oracle uses the identity column<\/a> for creating an auto increment column as follows:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> leave_requests (\n    request_id NUMBER<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span>,\n    employee_id INT<\/span> NOT<\/span> NULL<\/span>,\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    end_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    leave_type INT<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(request_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL auto increment column in PostgreSQL #<\/a><\/h2>\n\n\n\n
Similar to Oracle, PostgreSQL also uses the identity column<\/a> for defining auto increment columns:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> leave_requests (\n    request_id INT<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span>,\n    employee_id INT<\/span> NOT<\/span> NULL<\/span>,\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    end_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    leave_type INT<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(request_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL auto increment column in SQL Server #<\/a><\/h2>\n\n\n\n
SQL Server uses IDENTITY<\/a><\/code> property to define an auto increment column as shown in the following query:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> leave_requests (\n    request_id INT<\/span> IDENTITY<\/span>(1<\/span>,1<\/span>),\n    employee_id INT<\/span> NOT<\/span> NULL<\/span>,\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    end_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    leave_type INT<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(request_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL auto increment column in DB2 #<\/a><\/h2>\n\n\n\n
Like Oracle, DB2 uses the identity column for defining the auto-increment column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> leave_requests (\n    request_id INT<\/span> GENERATED<\/span> BY<\/span> DEFAULT<\/span> AS<\/span> IDENTITY<\/span>,\n    employee_id INT<\/span> NOT<\/span> NULL<\/span>,\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    end_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    leave_type INT<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(request_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the SQL auto increment column to define a column whose values are automatically generated sequential numbers.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t