From: Craig Ringer <craig@postnewspapers.com.au>
To: pgsql-sql@postgresql.org
Subject: Deferrable NOT NULL REFERENCES ... for two-way referential relationship
Date: Tue, 25 Mar 2008 16:55:10 +0900
Message-ID: <47E8AFDE.1080905@postnewspapers.com.au> (raw)
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 INITIALLY DEFERRED
)
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 INITIALLY DEFERRED;
... 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: craig@postnewspapers.com.au
Subject: Re: Deferrable NOT NULL REFERENCES ... for two-way referential relationship
In-Reply-To: <47E8AFDE.1080905@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox