Clever Geek Handbook

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?

+3


source to share


6 answers


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


source


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.



+2


source


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.

+1


source


Since the null value, being unknown, can be the same as the value in the primary key of another tuple

0


source


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.

0


source


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

-1


source






More articles:


All Articles