How can we say that AUTO_INCRMENT takes precedence over PRIMARY KEY in MySQL?

This can be understood with an example where a NULL value has been inserted into an AUTO_INCREMENT column and MySQL provides 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)

From the above example we can see that the column "id" has been declared NOT NULL along with the PRIMARY KEY bit, but it still accepts NULL values. This is because the column "id" has also been declared as AUTO_INCRMENT. So even if we insert NULL value in this column, MySQL will return the sequence number.

On the contrary, if we try to insert NULL in a column that is declared as PRIMARY KEY but not declared as AUTO_INCRMENT, then MySQL will return 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

Therefore, we can say that AUTO_INCRMENT takes precedence over PRIMARY KEY constraints, because in any case the AUTO_INCRMENT procedure needs to resolve earlier to check the PRIMARY KEY constraints.

The above is the detailed content of How can we say that AUTO_INCRMENT takes precedence over PRIMARY KEY in MySQL?. For more information, please follow other related articles on the PHP Chinese website!