How to create ID column in a database table?

In a table, an ID or identity column is a numeric column that identifies a row uniquely.

You don’t need to insert values for the ID column, an integer value will be automatically added each time a row is inserted.

How to create an ID column into a table?

In the following image, the user_id is the ID column of the users table. You can give any name to an ID column.

MySQL Dummy users table

Create a table with an ID column –

In the following SQL code you can see there is an ID column called user_id which data type is INT and NOT NULL (the field must not be empty).

After the NOT NULL you can see the AUTO_INCREMENT keyword – It helps to increment the user_id automatically, like 1, 2, 3, 4, …

Finally you can see the PRIMARY KEY (column_name) – It is a constraint that helps to uniquely identify each record in the table. PRIMARY KEY must have a UNIQUE value, and cannot contain NULL values.

CREATE TABLE IF NOT EXISTS users(
	user_id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(50),
    age TINYINT,
    email VARCHAR(50),
    PRIMARY KEY (user_id)
);

Insert some values to see the result

INSERT INTO users (name, age, email)
VALUES ('John', 25, '[email protected]'),
('Mark', 28, '[email protected]'),
('Smith', 34, '[email protected]'),
('Baburao', 53, '[email protected]'),
('Raju', 35, '[email protected]'),
('Shyam', 38, '[email protected]');
SELECT * FROM users;
users table with the ID column

How to add ID column to existing table?

We will use the ALTER command to edit an existing table.

First, lets see the table structure –

SELECT * FROM users;

users table without ID column

users table without id column

Syntax to add ID column to an existing table

ALTER TABLE table_name
ADD column_name_for_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (column_name_for_id);

Adding an ID column to the users table

ALTER TABLE users
ADD user_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (user_id);
users table with the ID column