CODEWITHSUNDEEP
mysql
avinash pandey
avinash pandey

Reputation: 1381

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

Upvotes: 7

Views: 23615

Answers (7)

Aditya
Aditya

Reputation: 1

Say, you have a table Employee(EID, isHeadFor) with "EID" as the primary key and "isHeadFor" as the foreign key referencing the primary key, "DeptID", of the table Department(DeptID). If DeptID takes a "NULL" value, then, if, say Employee with EID-123 is not the head for any department, then, we give "NULL" value to the field-isHeadFor corresponding to EID-123. But then, the problem is, by only looking at the table, we can't say whether Employee with EID-123 is the head for no department or is the head of the department "NULL".

Upvotes: 0

jinu
jinu

Reputation: 1

Suppose after performing some experiments you get to know about the unknown null value.

What if that value is equal to the value of some other primary key then it cannot be in the relation to avoid this primary key values are not assigned null.

Moreover any comparison with the null is mostly false.

Upvotes: 0

P.Akondo
P.Akondo

Reputation: 53

Because a null value, being unknown, might be the same as the value in the primary key of another tuple

Upvotes: 0

velocity
velocity

Reputation: 2096

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)   
};

Upvotes: 7

vishal
vishal

Reputation: 1

This is the main difference between primary key and unique key.

Upvotes: -2

Sagar Joon
Sagar Joon

Reputation: 1417

Primary Key is used to identify rows uniquely in a table which cannot be null while Unique key may contain null value as per SQL rules.

For Example,

A table contains record of school children data like following :

Roll_NO | Name | Class | Address | School_Bus_ID

Here , Roll_NO should not contain any null value as it will be used to identify a student in school . And School_Bus_ID may contain some null value as some children may opt for own transport rather than school bus.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311723

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.

Upvotes: 2

Related Questions