Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why primary key cannot contain null values?

Tags:

mysql

I have read that mysql puts a constraint of not null on primary key , but unique key allows one null value for a column. So why not primary key also allows a null value??

like image 233
avinash pandey Avatar asked Sep 06 '14 14:09

avinash pandey


2 Answers

A PRIMARY KEY column is equivalent to UNIQUE and NOT NULL and is indexed column by default.
It should be UNIQUE because a primary key identifies rows in a table so 2 different row should not have the same key.
In addition a primary key may be used a FOREIGN KEY in other tables and that's why it cannot be NULL so that the other table can fin the rows in the referenced table.

For example:

CREATE person{   
   id INT PRIMARY KEY,  -- equals UNIQUE NOT NULL   
   name VARCHAR(20)   
};   

CREATE family{   
   id INT PRIMARY KEY,  -- equals UNIQUE NOT NULL   
   menber_id INT FOREIGN KEY REFERENCE person(id)   
};   
like image 104
velocity Avatar answered Sep 20 '22 14:09

velocity


A primary key must uniquely identify a record - i.e., each record can be expressed in the terms of "the record which has a key that equals X". Since null is not equal to any value, it cannot be used as a primary key.

like image 37
Mureinik Avatar answered Sep 20 '22 14:09

Mureinik