The ORM Foundation

Hi Andy,

You don't need a second subset constraint because 'has first' is mandatory.

I'm really more interested in your statement that Primary Key columns should never contain NULLs.  Why?  I'm happy to create tables with NULLs in PKs and FKs if it achieves what I need.  (Not that it happens often, mind.)  (I always guarantee my databases to at least 3NF/BCNF which I find adequate and practical for most cases.)  So I'd very much like to know what the issue is with them.

thanks, Peter

Yes. You are correct. The subset constraint would be implied by virtue of that mandatory role constraint. My Bad.

I guess my statement about PKs not containing NULLs stems from a lack of experience. I've never gone there. All I could see would be pain. I could not imagine an identifying attribute combination which contains unkown values.

If you say that it is workable, then I am satisfied with that.

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  

Thanks, Ken.  That was a good explanation.

(After I hit the Post button I realised that I had stuffed up my wording - which means I really shouldn't Post when I'm tired.)  On the occasion I have had nulls in keys, they were always composite keys, and were mainly used to hold the results of queries with outer joins, rather than be the primary source of data.

regards, Peter

Hi Peter,

Thanks for the acknowledgement.
Since we have gone this far I feel that I must respond to your last point as well.

As I tried to explain in my earlier post, by definition, a key cannot contain nulls.
So, whatever the collection of data that you are talking about, if it has nulls - then it is not a key.

Hope this helps.
Ken

Real life is sometimes more complicated than one might wish. There are many examples in practice where disjunctive reference schemes are used (the botanical example in both editions of my book is a simplified version of one of these cases).