Method 1: use AUTO_INCREMENT

AUTO_INCREMENT can be used when creating a table or when adding a new AUTO_INCREMENT column to an existing table.

Note that PRIMARY KEY must always be specified together with AUTO_INCREMENT. Otherwise you'll get the following error.

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key.

1.1 Use AUTO_INCREMENT when creating a table

A unique number is automatically created for a table when AUTO_INCREMENT function is used in a CREATE TABLE statement. It instructs MySQL to automatically create and increment an integer number when new records are inserted into the table. The initial value will start at 1 and increment by 1 with each insert, unless otherwise specified.

For example, we create an exam_result table as below. ID column is auto incremented.

-- Create table
CREATE TABLE exam_result
(
   ID int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
   StudentName varchar(20) NOT NULL,
   ClassName varchar(20) NOT NULL,
   StudentMark tinyint NOT NULL
);

Now adding some data to the table. Note that, we don't need to specify ID column in the INSERT statement if you specify other column names because ID is auto maintained.

-- Insert data to the table
insert into exam_result(StudentName,ClassName,StudentMark)
values('Anne','Class A',92),
('Gary','Class A',85),
('Tom','Class A',85),
('John','Class A',76),
('Jerry','Class B',95),
('Nick','Class B',91),
('Joe','Class B',88),
('Ted','Class B',88),
('Bob','Class B',82);

Or, if you omit all the column names in the INSERT statement, use keyword NULL in the value list as the value for the ID column. This way, ID will be assigned to the next available sequence number.

insert into exam_result values(NULL,'Anne','Class A',92),
(NULL,'Gary','Class A',85),
(NULL,'Tom','Class A',85),
(NULL,'John','Class A',76),
(NULL,'Jerry','Class B',95),
(NULL,'Nick','Class B',91),
(NULL,'Joe','Class B',88),
(NULL,'Ted','Class B',88),
(NULL,'Bob','Class B',82);

9 records inserted:

1.2 Add AUTO_INCREMENT column to an existing table

Suppose when first creating our exam result table, we don't have an ID column.

-- Create the table without ID column
CREATE TABLE exam_result
(
   StudentName varchar(20) NOT NULL,
   ClassName varchar(20) NOT NULL,
   StudentMark tinyint NOT NULL
);

-- Adding sample data
insert into exam_result values('Anne','Class A',92),
('Gary','Class A',85),
('Tom','Class A',85),
('John','Class A',76),
('Jerry','Class B',95),
('Nick','Class B',91),
('Joe','Class B',88),
('Ted','Class B',88),
('Bob','Class B',82);

Now we execute an ALTER TABLE query to add an AUTO_INCREMENT ID column.

-- Add the ID column
alter table exam_result 
add column ID int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY;

Note that when executing the alter table statement, ID column is automatically populated with unique IDs at the same time. Now run a select * from exam_result to see the ID column has been filled with unique IDs already.

9 records returned:

Method 2: use ROW_NUMBER function

You can also use ROW_NUMBER function to create a unique ID column. Normally ROW_NUMBER is used to add unique numbers to records with a group, but if you treat the entire table as one group, a unique number can be added to each row across the entire table, similar to what AUTO_INCREMENT does.

The advantage of using ROW_NUMBER as opposed to the use of AUTO_INCREMENT is that the IDs generated by ROW_NUMBER can have business meanings in it. That is it generates the unique IDs based on ranking of values in another column. But if you don't need such advantage, you can treat the unique IDs artificial or surrogate.

Query below does not use PARTITION BY clause which literally treats all classes as one group, so it assigns a unique and incremented number to each student based on their marks regardless of which class they belong to.

SELECT StudentName,
	ClassName,
	StudentMark,
	ROW_NUMBER() OVER (ORDER BY StudentMark DESC) as StudentRank
FROM exam_result;

9 records returned:

Happy Coding!

Other tutorials in this category

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 2

3. MySQL Northwind Queries - Part 3

4. How to Work with Two Unrelated Values

5. How to Fill Gaps in Sales Data

6. How to Calculate Totals, Subtotals and Grand Total

7. How to Work with NULL Values

8. How to fill down empty cells with values from a previous non-empty row

9. Use RANK function to update a previous record within a group or partition

10. 3 ways to get Top N rows from MySQL

11. How to generate Cumulative Sum (running total) by MySQL - Part 1

12. How to generate Cumulative Sum (running total) by MySQL - Part 2

Back to Tutorial Index Page