MySQL: All parts of primary key must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Question

I have a problem with MySQL. I have created Database called 'BucketList', and then I have tried to create table called 'tbl_user', it looks like that:

CREATE TABLE `BucketList`.`tbl_user` (
  `user_id` BIGINT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(45) NULL,
  `user_username` VARCHAR(45) NULL,
  `user_password` VARCHAR(45) NULL,
  PRIMARY KEY (`user_id`));

After that I met a problem: "All parts of primary key must be NOT NULL; if you need NULL in a key, use UNIQUE instead"

Do you have any idea guys, what's wrong? MySQL version that I am using is:

mysql --version
mysql Ver 14.14  Distrib 5.7.12, for Win64 (x86_64)

Answer 1

As of MySQL 5.7, it no longer supports null values for the primary key.

See the documentation here:

A unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY.

So, your user_id key cannot be null if it's going to be used as a primary key. You should declare it as non-nullable:

CREATE TABLE `BucketList`.`tbl_user` (
  `user_id` BIGINT AUTO_INCREMENT,
  `user_name` VARCHAR(45) NULL,
  `user_username` VARCHAR(45) NULL,
  `user_password` VARCHAR(45) NULL,
PRIMARY KEY (`user_id`));

(Notice the lack of NULL after BIGINT)