Hi all
I'm running into an issue with my schema where I'm trying to establish a
mandatory two-way relationship between two entities, and I'm hoping for
some external wisdom. Using "customer" and "address" by way of example:
CREATE TABLE customer (id INTEGER PRIMARY KEY,address_id INTEGER NOT NULL REFERENCES address(id) DEFERRABLE
INITIALLYDEFERRED
)
CREATE TABLE address (id INTEGER PRIMARY KEY,customer_id INTEGER NOT NULL REFERENCES customer(id)
)
Every customer must have one or more addresses, ie address_id must be
set to a valid address by commit time. It does not matter which address
it's set to (though in this particular app there is in fact a preferred
address).
If it could be written as a CHECK constraint I'd be trying to enforce:
CONSTRAINT address_id_exists CHECK ( EXISTS (SELECT 1 FROM address WHERE address.id = address_id) ) DEFERRABLE
INITIALLYDEFERRED;
... on the customer table.
PostgreSQL supports DEFERRABLE INITIALLY DEFERRED for the foreign key
constraint, but unless I'm missing something it doesn't appear to have
any direct way to make the NOT NULL check deferrable.
There are constraint triggers:
http://www.postgresql.org/docs/8.3/static/sql-createconstraint.html
and I could use one to enforce the NOT NULL on the table as a whole (at
an acceptable cost in this case). However, I've seen some posts like
this one:
http://archives.postgresql.org/pgsql-hackers/2005-01/msg00882.php
that suggest that constraint triggers are or have been deprecated.
However, their removal is no longer on the TODO as far as I can tell.
Are constraint triggers a safe and reasonably future proof way to
implement this, given that I'm quite OK with being tied to postgresql?
Is there some better way that I'm missing?
Is what I'm trying to do crazy for some reason I haven't spotted?
--
Craig Ringer