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);
INSERT INTO maildomains (domain, profile ) VALUES ('example.com',
'active');
INSERT INTO mailusers (username,domain) VALUES ('postmaster',
'example.com');
INSERT INTO mailboxes (username,domain, mailmessage) VALUES
('postmaster', 'example.com', 'Hello');
DELETE FROM mailusers ;
===>>> ERROR: SQL "UPDATE ONLY "public"."mailboxes"........... etc...
But:
UPDATE mailboxes SET username = null;
DELETE FROM mailusers ;
===>>> OK!!!
SELECT * from mailboxes ;
username | domain | mailmessage
----------+-------------+--------------
| example.com | Hello
------------------------------END testcase
I tried a TRIGGER BEFORE DELETE on table mailuser to set the username
being deleted to NULL (and simulate the above OK example part), but the
update does not propagate along constraints before constraint error is
detected and the whole delete get aborted by postgres.
Is there a way to implement that sort of referrential constraints (i.e.:
just partially "set null on delete")?
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?
Thenx,
-R