Why MySQL NOT NULL shouldn’t be added to primary key field?


You do not need to add NOT NULL to primary key field because it gets NOT NULL automatically. Primary key is combination of both NOT NULL and Unique Key.

Here is the demo of primary key field. Let us first create a table. The query to create a table is as follows:

mysql> create table NotNullAddDemo
   -> (
   -> Id int AUTO_INCREMENT,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.81 sec)

In the above table, you do not need to add NOT NULL to primary key field because MySQL internally converts it into NOT NULL. To check if it is correct or not, use the following syntax.

DESC yourTableName;

Let us now check the above syntax to get the table description:

mysql> desc NotNullAddDemo;

The following is the output:

+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| Id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.07 sec)

Look at the column Null, there is a value NO that means there is no NULL value.

To check for NOT NULL, let us insert some NULL records in the table. This will allow NULL value but every time it counts the value from 1. The query to insert records in the table.

mysql> insert into NotNullAddDemo values(NULL);
Query OK, 1 row affected (0.12 sec)
mysql> insert into NotNullAddDemo values(NULL);
Query OK, 1 row affected (0.45 sec)

After inserting two NULL values for primary key, if you try to insert value 1, then an error will generate. This is because MySQL counts first NULL value as 1 and second NULL value as 2 and so on.

The error is as follows if you will now try to insert 1:

mysql> insert into NotNullAddDemo values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

If you insert value 3 then it will accept:

mysql> insert into NotNullAddDemo values(3);
Query OK, 1 row affected (0.21 sec)
mysql> insert into NotNullAddDemo values(NULL);
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from NotNullAddDemo;

The following is the output:

+----+
| Id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

Updated on: 30-Jul-2019

604 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements