Re: partial "on-delete set null" constraint - Mailing list pgsql-general

W dniu 03.01.2015 o 14:11, Alban Hertroys pisze:
[------------------]
> You assumed a functional dependency between username and domain, while those fields actually describe independent
entitiesthat don’t necessarily go together as you found out. Hence you need to normalise further. 
>
> For example:
>
> CREATE TABLE maildomains (domain text primary key, profile text not null);
> CREATE  TABLE mailusers (username text primary key);
> CREATE  TABLE maildomainusers (username text references mailusers(username), domain text references
maildomains(domain),primary key (username, domain)); 
> CREATE  TABLE mailboxes (username text references mailusers(username) on update cascade on delete set null, domain
textnot null references maildomains(domain) on update cascade, mailmessage text not null); 

I don't think that this tableset actually describe "an ordinary
mailhub", which I'm coding.

the "on delete set null" within mailboxes(username) act only on delete
executed at mailusers; while the delete in question will be executed on
maildomainusers.

In particular "postmaster", as a single entity in mailusers table, will
have as many entries in maildomainusers as there are domains in
maildomains. But some domains may live without a postmaster user ... or
a postmaster user may be replaced by an alias (another table, not
presented for clearity). in such case, postmaster user will be dropped
from maildomainusers, but will remain in mailusers table for other
domains to reference. And delete of that postmaster user from
maildomainuser will not fireback into the mailboxes to set null
postmaster username from mails within that domain.

This additional level of normalization solves me anything, I think.

>
>> Is there a way to implement that sort of referrential constraints (i.e.: just partially "set null on delete”)?
> Not as a foreign key reference delete action.

Pity. So I must look for some sort of trigger functions .... as I've
already started, but nothing came up functioning as I'd need it to.

>
>> Would it violate SQL standard (signifficantly), if an "on delete set null" action just ignored all the FK columns
thathave a "NOT NULL" constraint set? 
> Yes. You would end up with a non-unique reference to the foreign table, as the tuple (domain, NULL) could reference
_any_mailuser in a domain: NULL means ‘unknown’, any username might match that. 

Yes. This is precisely the "semantics" I'm trying to put into the
schema: after a username is "released" from service, all it's messages
become "from unknown user".... unless thoroughly investigated :)


>
> As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure
he’sright about that. 
>

Having only slight theoretical background, I'd say: it could be
"partially" the reason. I think, that "primary key" is just a syntactic
shortcut for "unique AND not null" - so often used, that the shortcut is
so appreciated. But "just unique", meaning unique just for values that
"happen to be known" is also usefull, and thus it is allowed on equal
bases.... only for other usage scenarios.

-R