Search code examples
mysqlprimary-keyauto-incrementnotnull

NOT NULL not working on primary key with auto increment

Why NOT NULL in column definition does not work if that column has both PRIMARY KEY and AUTO_INCREMENT? Tested on MySQL 5.6.

CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

INSERT INTO test VALUES (NULL);

SELECT * FROM test;

Result:

id
1
Solution

  • Well, as seen in the mysql page:

    If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers

    Using AUTO_INCREMENT

    That is the expectec behaviour.