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?
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.
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.
NULL == NULL -> false (at least in DBMSs)
So you wouldn't be able to retrieve any relationships using a NULL value even with additional columns with real values.
