Re: partial "on-delete set null" constraint
От | Alban Hertroys |
---|---|
Тема | Re: partial "on-delete set null" constraint |
Дата | |
Msg-id | C16AA6CD-7443-49EF-B4A5-C98A5CD8AE70@gmail.com обсуждение исходный текст |
Ответ на | partial "on-delete set null" constraint (Rafal Pietrak <rafal@ztk-rp.eu>) |
Ответы |
Re: partial "on-delete set null" constraint
(Rafal Pietrak <rafal@ztk-rp.eu>)
Re: partial "on-delete set null" constraint (Alban Hertroys <haramrae@gmail.com>) |
Список | pgsql-general |
On 02 Jan 2015, at 13:31, Rafal Pietrak <rafal@ztk-rp.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 usernamefrom 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 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 text notnull 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 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. 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. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
В списке pgsql-general по дате отправления: