What's the point of adding NOT NULL to primary key field in MySQL?

Question

What's the point of adding NOT NULL to a primary key field? Primary key is already not null + unique.

Here is an example:

CREATE TABLE student (
  id int(11) AUTO_INCREMENT NOT NULL,
  name varchar(255),
  PRIMARY KEY(id)
)

Why not to define it like this instead:

CREATE TABLE student (
  id int(11) AUTO_INCREMENT,
  name varchar(255),
  PRIMARY KEY(id)
)

Quirinus

Answer 1

They are the same. Primary key got NOT NULL automatically.

Qualifier

soo why peaple add NOT NULL?? – Quirinus

@Yosef this is just a matter of style. And mysqldump add this NOT NULL when dump a table, because of a bug for allowing NULL in mysql. – Qualifier

Answer 2

You are asking, why do people bother adding the NOT NULL when it is unnecessary? Just because it is good style, I guess. And makes it explicit to the reader.

Ratliff

Answer 3

NULL is not equivalent to NULL(as NULL indicates an unknown or absent value), so you will be permitted to have multiple records that have NULL for the id, even though there's a primary key / unique constraint defined, hence the use of NOT NULL. That's if MySql even allows you to define a primary key on a nullable field.

In addition, as a primary key is often used in a foreign key in other tables, having one or more NULL values wouldn't make sense.

Sexuality

NULL on PRIMARY KEY violate ISO standard and considered as a bug in mysql: bugs.mysql.com/bug.php?id=390 . See en.wikipedia.org/wiki/Unique_key for the justification of NOT NULL – Qualifier

Answer 4

If PRIMARY KEY is declared without NOT NULL, NOT NULL is added to PRIMARY KEY implicitly so PRIMARY KEY with or without NOT NULL is the same.

The MySQL documentation says as shown below:

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. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

And also, the MySQL documentation says as shown below:

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. ...

Laurenlaurena