From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Rafal Pietrak <rafal(at)ztk-rp(dot)eu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: partial "on-delete set null" constraint |
Date: | 2015-01-03 13:11:59 |
Message-ID: | C16AA6CD-7443-49EF-B4A5-C98A5CD8AE70@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 02 Jan 2015, at 13:31, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
>
> Hello,
>
> Rewriting my mail-hub I fell into the following problem:
> 1. I have a table with mailmessages, which has an FK to a table of hub users.
> 2. I'd like to retain the content of message repository (with it's domain key not cleared), when I drop a particular username from service .... to release that username to others.
> 3. I try to do that with FK "on-update/on-delete" actions, but to no avail:
>
> testcase-------(against postgresql v9.1 hosted by debian)---------------------------
> CREATE TABLE maildomains (domain text primary key, profile text not null);
> CREATE TABLE mailusers (username text , domain text references maidomains(domain), primary key (username, domain));
> CREATE TABLE mailboxes (username text , domain text not null, mailmessage text not null , foreign key (username, domain) references mailusers (username,domain) on update cascade on delete set null);
You assumed a functional dependency between username and domain, while those fields actually describe independent entities that 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 text not null references maildomains(domain) on update cascade, mailmessage text not null);
> 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.
> Would it violate SQL standard (signifficantly), if an "on delete set null" action just ignored all the FK columns that have 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.
As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure he’s right about that.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
From | Date | Subject | |
---|---|---|---|
Next Message | Rafal Pietrak | 2015-01-03 14:20:14 | Re: partial "on-delete set null" constraint |
Previous Message | Rafal Pietrak | 2015-01-03 08:49:30 | Re: partial "on-delete set null" constraint |