- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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.
- 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?
- How to create an INT field (not a primary key) that begins at 1000 with auto_increment in MySQL?
- Is it compulsory to set PRIMARY KEY for AUTO_INCREMENT value?
- How can we set PRIMARY KEY on multiple columns of a MySQL table?
- Can we use PRIMARY KEY( column1, column2) in MySQL to make pairs?
- How can we set PRIMARY KEY on multiple columns of an existing MySQL table?
- How can we change MySQL AUTO_INCREMENT starting number?
- What do you mean by PRIMARY KEY and how can we use it in MySQL table?
- How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
- How can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?
- How can we apply the PRIMARY KEY constraint to the field of an existing MySQL table?
- How can we say that Energy= Stored work?
- When are two tables connected with MySQL FOREIGN KEY then how can we say that the integrity of data is maintained in child table?