Hi Peter,
PeterC: I'm really more interested in your statement that Primary Key columns should never contain NULLs. Why?
Here is the answer to your question:
1. As Terry explains in his book, the term key means "a minimal combination of columns where no duplicates are allowed. (Halpin & Morgan 2008:47)
The term "key" is from the principles of the relational model of data proposed by the late EF Codd in 1969. (Codd 1970)
2: The term "primary key" is based on the notion that a relational table may have more than one "key". If so, each of these keys is called a "candidate key" because each candidate key offers an alternative means of uniquely identifying a row in the table. If more than one candidate key exists then one of the candidate keys must be designated as the primary key. (Halpin & Morgan 2008:476)
So my first observation is that your statement "I'm happy to create tables with NULLs in PKs and FKs" seems to have a number of problems.
For example, lets say that you have a table that uses a single column as a key.
If you allow that column to have nulls (lets say there are three rows with nulls) then it is not possible to uniquely identify any of the three rows.
By extension, this principle also applies to keys that span more than one column.
Now - regarding your comments about "I always guarantee my databases to at least 3NF/BCNF"
If you Google for definitions of 3NF you will find statements such as this for BCNF: "Each attribute must represent a fact about the key, the whole key, and nothing but the key"
So, by definition, 3NF/BCNF require keys to be unique identifiers for rows.
The idea of "having nulls in a unique identifier" seems a bit of a contradiction to me - but I'm always willing to learn.
So I'm really curious - how can you "guarantee 3NF" if you allow what you are choosing to call a "key" to contain nulls?
Ken
References:
Codd, E F, 1970, A Relational Model of Data for Large Shared Data Banks, Journal of the Association of Computing Machinery, Volume 13, Number 6, June 1970 http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
Halpin, T. & Morgan, T. 2008, Information Modeling and Relational Databases:Second Edition, Morgan Kaufmann, San Francisco