How do you create a table with an identity column and set the starting value?
DavidLee
Posted by DavidLee
Last Updated: July 16, 2024
SQL Identity
In SQL, you can create a table with an identity column using the IDENTITY property in your CREATE TABLE statement. The IDENTITY property allows you to define an auto-incrementing column where you can specify the starting value and the increment value. Here's the basic syntax for creating a table with an identity column, including setting the starting value: 
CREATE TABLE TableName (
    Column1 INT PRIMARY KEY IDENTITY(StartingValue, IncrementValue),
    Column2 DataType,
    Column3 DataType
    ...
);
Example
If you want to create a table called Employees with an identity column named EmployeeID, starting at 1000 and incrementing by 1, your SQL statement would look like this: 
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1000, 1),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);
Explanation:
- EmployeeID INT PRIMARY KEY: This defines the column EmployeeID as an integer and a primary key for the table. - IDENTITY(1000, 1): This specifies that the EmployeeID will start at 1000 and will increment by 1 for each new record.
Notes:
- If you don't specify an increment value, it defaults to 1. - The syntax may vary slightly between different SQL databases (like SQL Server, MySQL, PostgreSQL, etc.), but the concept of an identity column remains similar. - In MySQL, you use AUTO_INCREMENT, and in PostgreSQL, you can achieve similar functionality using SERIAL or BIGSERIAL. Here's an example for MySQL:
MySQL Example
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
) AUTO_INCREMENT=1000;  -- Start the AUTO_INCREMENT at 1000
The above MySQL example sets the starting value for the AUTO_INCREMENT column to 1000 for new records.
Default Image