Why can't the primary key contain null values?
I read that mysql puts a non-null constraint on the primary key, but the unique key allows one null value per column. So why isn't the primary key also nullable?
The PRIMARY KEY column is equivalent to UNIQUE, not NULL and the default indexed column.
It must be UNIQUE because the primary key identifies the rows in the table, so no two different rows must have the same key.
Also, the primary key can be used by FOREIGN KEY in other tables and why it cannot be NULL so that another table can truncate 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)
};
The primary key must uniquely identify the record, that is, each record can be expressed in terms of "a record that has a key equal to X". Since null
it is not equal to any value, it cannot be used as a primary key.
Primary key is used to uniquely identify rows in a table that cannot be null, and Unique key can contain null according to SQL rules.
For example,
The table contains a record of data of school children, for example:
Roll_NO | Name | Class | Address | School_Bus_ID
Here Roll_NO should not contain any null value, as it will be used to identify the student at the school. And School_Bus_ID may contain some null, as some children may choose their own transport rather than the school bus.
Since the null value, being unknown, can be the same as the value in the primary key of another tuple
Let's assume that after doing some experimentation, you find out about an unknown null value.
What if this value is equal to the value of some other primary key, then it cannot be in a relationship, so as to avoid that the primary key values are not assigned NULL values.
Moreover, any comparison with zero is false in most cases.
This is the main difference between a primary key and a unique key.