Re: partial "on-delete set null" constraint

> On 03 Jan 2015, at 23:14, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
>> Well, that’s embarrassing, it’s only a few weeks since I learned this and I’m already attributing the theory to the wrong database deity! That’s Codd-relationality, of course. Not Boyce.
>
> The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says:
> rule 3: "systematic treatment of null values"; hmmm.... this is a little broader then "support for null". I would think, that:
> 1. if a sequence of "update XX set fk_field=null; then delete YY depending on that FK", for a particular schema definition works ...
> 2. so the implementation of FK should support that too ... to be called "systematic", right?
> 3. and the simplest way to do that for the case at hand, within an "on delete action", is to skip those parts of FK, that are marked as "not null" within the referring table. That would be a "requirement" for rdbms implementation that claims compliance with Codd rule nr.3 :)

I translated Codd-relationality to English, possibly it’s named differently.

Oddly enough, I can’t find any reference to Codd being responsible for this rule anywhere on the internet. What I did find is that the theory I referred to stems from 1970(!), but that’s the closest I got.
Anyway, wikipedia has the following to say about the issue, although their explanation is a bit obtuse concerning the definition of candidate keys from which to choose a primary key (http://en.wikipedia.org/wiki/Unique_key)

"A table can have at most one primary key, but it may have more than one candidate key. A primary key is a combination of columns which uniquely specify a row; it is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not."

They blatantly leave out why primary keys have that implicit NOT NULL! Is this some forgotten about piece of database theory? I’ll have to ask my teachers where they got their definition from!

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.