Re: partial "on-delete set null" constraint

> 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:

Interesting discussion on database theory!

If you switched to surrogate keys for all your tables, you could
soft-delete accounts with a deleted_at column, and then have a unique
index on username+domain (or username+domain_id?) that is WHERE
deleted_at IS NULL. Does that work? Probably you want the same
approach for the maildomains table to enforce unique non-deleted
domains.

It seems like if you want to retain data for auditing, you don't
really want to delete *anything*, including the username. Surrogate
keys and a partial unique index would let you do that I believe.

Paul

--
_________________________________
Pulchritudo splendor veritatis.