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.
Published on 19-Feb-2018 18:01:20
