How can we say that in MySQL, AUTO_INCREMENT is taking precedence over PRIMARY KEY?

This can be understood with the help of an example in which NULL value has been inserted in an AUTO_INCREMENT column and MySQL deliver a new sequence number.

mysql> Create table employeeinfo(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Name Varchar(10));
Query OK, 0 rows affected (0.16 sec)

mysql> Insert into employeeinfo(id, Name) values(NULL, 'Saurabh');
Query OK, 1 row affected (0.07 sec)

mysql> Select * from employeeinfo;
+----+---------+
| id | Name    |
+----+---------+
| 1  | Saurabh |
+----+---------+
1 row in set (0.00 sec)

As we can observe from the above example that the column ‘id’ has been declared NOT NULL as well as PRIMARY KEY bit still it accepts a NULL value. It is because column ‘id’ has been declared as AUTO_INCREMENT also. Due to this, MySQL returns sequence number even when we insert NULL value in that column.

In Contrast, if we will try to insert NULL in a column which is declared PRIMARY KEY but not AUTO_INCREMENT then MySQL returns an error as follows −

mysql> create table people(id INT primary key, name varchar(10));
Query OK, 0 rows affected (0.18 sec)

mysql> Insert into people(id, name) values(NULL, 'Rahul');
ERROR 1048 (23000): Column 'id' cannot be null

Hence, we can say that AUTO_INCREMENT precedes the PRIMARY KEY constraint because AUTO_INCREMENT process would need to be resolved earlier to checking of PRIMARY KEY constraint in any case.

Varun

Published on 19-Feb-2018 13:31:20

• Related Questions & Answers
• How to change a primary key in MySQL to auto_increment?
• MySQL ALTER column to remove primary key and auto_increment?
• Can we remove a primary key from MySQL table?
• Is it compulsory to set PRIMARY KEY for AUTO_INCREMENT value?
• How to create an INT field (not a primary key) that begins at 1000 with auto_increment in MySQL?
• How can we say, dying in sleep is peaceful?
• Can we use PRIMARY KEY( column1, column2) in MySQL to make pairs?
• How can we set PRIMARY KEY on multiple columns of a MySQL table?
• How can we change MySQL AUTO_INCREMENT starting number?
• When are two tables connected with MySQL FOREIGN KEY then how can we say that the integrity of data is maintained in child table?
• How can we set PRIMARY KEY on multiple columns of an existing MySQL table?
• Remove Primary Key in MySQL?
• Reset Primary Key in MySQL
• Is the primary key automatically indexed in MySQL?
• How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?