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.
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;
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
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);
