MySQL is not failing when deliberately inserting `NULL` in Primary Key AUTO_INCREMENT column

Question

I have created a table empInfo as follow

CREATE TABLE empInfo (
  empid INT(11) PRIMARY KEY AUTO_INCREMENT  ,
  firstname VARCHAR(255) DEFAULT NULL,
  lastname VARCHAR(255) DEFAULT NULL
 )

Then I run below Insert statements :-

INSERT INTO empInfo VALUES(NULL , 'SHREE','PATIL');
INSERT INTO empInfo(firstname,lastname) VALUES( 'VIKAS','PATIL');
INSERT INTO empInfo VALUES(NULL , 'SHREEKANT','JOHN');

I thought first or Third SQL will fail as empid is PRIMARY KEY and We are trying to insert NULL for empid .

But MYSQL proved me wrong and all 3 queries ran successfully .

I wanted to know Why it is not failing when trying to insert NULL in empid column ?

Final Data available in table is as below

empid   firstname   lastname
1       SHREE       PATIL
2       VIKAS       PATIL
3       SHREEKANT   JOHN

I can figure out that it has something releted to AUTO_INCREMENT But I am not able to figure out reason for it . Any pointers on this .

by default you have set it has auto incremented....i hop you have to give not null — Vamshi .goli, Jan 09 '15 at 05:22
Does that mean AUTO_INCREMENT is taking precedance over PRIMARY KEY ? — Shirishkumar Bari, Jan 09 '15 at 05:56

score 4 · Accepted Answer · edited May 23 '17 at 12:02

This behaviour is by design, viz inserting 0, NULL, or DEFAULT into an AUTO_INCREMENT column will all trigger the AUTO_INCREMENT behaviour.

INSERT INTO empInfo VALUES(DEFAULT, 'SHREEKANT','JOHN');
INSERT INTO empInfo VALUES(NULL, 'SHREEKANT','JOHN');
INSERT INTO empInfo VALUES(0, 'SHREEKANT','JOHN');

and is commonplace practice

Note however that this wasn't however always the case in versions prior to 4.1.6

Edit

Does that mean AUTO_INCREMENT is taking precedance over PRIMARY KEY?

Yes, since the primary key is dependent on the AUTO_INCREMENT delivering a new sequence prior to constraint checking and record insertion, the AUTO_INCREMENT process (including the above re-purposing of NULL / 0 / DEFAULT) would need to be resolved prior to checking PRIMARY KEY constraint in any case.

If you remove the AUTO_INCREMENT and define the emp_id PK as INT(11) NULL (which is nonsensical, but MySql will create the column this way), as soon as you insert a NULL into the PK you will get the familiar

Error Code: 1048. Column 'emp_id' cannot be null

So it is clear that the AUTO_INCREMENT resolution precedes the primary key constraint checks.

paxdiablo · Answer 2 · 2015-01-09T05:43:23.727

As per the documentation page:

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers. If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers.

So, because you have an auto increment null-allowed field, it ignores the fact that you're trying to place a NULL in there, and instead gives you a sequenced number.

You could just leave it as is since, even without the not null constraint, you can't get a NULL in there, because it will auto-magically convert that to a sequenced number.

Or you can change the column to be empid INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL if you wish, but I still think the insert will allow you to specify NULLs, converting them into sequenced numbers in spite of what the documentation states (tested on sqlfiddle in MySQL 5.6.6 m9 and 5.5.32).

In both cases, you can still force the column to a specific (non-zero) number, constraints permitting of course.

score 2 · Answer 3 · answered Jan 09 '15 at 05:23

2

It is exactly because of the auto increment. As you can see, no empid values are null in the db. That is the purpose of auto increment. Usually you would just not include that column in the insert, which is same as assigning null

answered Jan 09 '15 at 05:23

jamador

139
4

1

Does that mean AUTO_INCREMENT is taking precedance over PRIMARY KEY ? – Shirishkumar Bari Jan 09 '15 at 05:55
@Shree, not necessarily taking precedence, perhaps happening _first._ Unless that's your definition of "taking precedence". The NULL is converted to a valid sequence due to the auto increment, after which there's no problem in the primary key (or not null) constraints. – paxdiablo Jan 09 '15 at 06:01

score 0 · Answer 4 · answered Jan 09 '15 at 05:22

0

CREATE TABLE empInfo (
  empid INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
  firstname VARCHAR(255) DEFAULT NULL,
  lastname VARCHAR(255) DEFAULT NULL
 )

Not sure but i think it will work :)

answered Jan 09 '15 at 05:22

Tiger

404
1
4
13

Even if I define `empid INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL` Its Still inserting row without failing. – Shirishkumar Bari Jan 09 '15 at 05:54

MySQL is not failing when deliberately inserting `NULL` in Primary Key AUTO_INCREMENT column

4 Answers4

Linked