avinash pandey avinash pandey - 4 months ago 59
MySQL Question

Why primary key cannot contain null values?

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??


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)