Menu
ORACLE does not permit NULL values in any of the columns that comprise a primary key. It appears that the same is true of most other "enterprise-level" systems.
At the same time, most systems also allow unique contraints on nullable columns.
Why is it that unique constraints can have NULLs but primary keys can not? Is there a fundamental logical reason for this, or is this more of a technical limitation?
306
In composite primary key columns you cannot pass null values. Each column defined as a primary key would be validated so that null values are not passed on to them. If you have given a Unique constraint then we have a chance of NULL values being accepted. But in case of primary keys they cannot hold null values.
A primary key defines the set of columns that uniquely identifies rows in a table. When you create a primary key constraint, none of the columns included in the primary key can have NULL constraints; that is, they must not permit NULL values.
Unlike other types of keys, a foreign key does not require an index on its underlying column or columns. A table can have zero or more foreign keys. The value of a composite foreign key is null if any component of the value is null.
There is no conclusion that composite primary keys are bad. The best practice is to have some column or columns that uniquely identify a row. But in some tables a single column is not enough by itself to uniquely identify a row. SQL (and the relational model) allows a composite primary key.
Primary keys are for uniquely identifying rows. This is done by comparing all parts of a key to the input.
Per definition, NULL cannot be part of a successful comparison. Even a comparison to itself (NULL = NULL) will fail. This means a key containing NULL would not work.
Additonally, NULL is allowed in a foreign key, to mark an optional relationship.(*) Allowing it in the PK as well would break this.
(*)A word of caution: Having nullable foreign keys is not clean relational database design.
If there are two entities A and B where A can optionally be related to B, the clean solution is to create a resolution table (let's say AB). That table would link A with B: If there is a relationship then it would contain a record, if there isn't then it would not.
80
A primary key defines a unique identifier for every row in a table: when a table has a primary key, you have a guranteed way to select any row from it.
A unique constraint does not necessarily identify every row; it just specifies that if a row has values in its columns, then they must be unique. This is not sufficient to uniquely identify every row, which is what a primary key must do.
28
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
