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)
};
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With