Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
74 Views
