Skip site navigation (1) Skip section navigation (2)

Deferrable NOT NULL REFERENCES ... for two-way referential relationship

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: pgsql-sql(at)postgresql(dot)org
Subject: Deferrable NOT NULL REFERENCES ... for two-way referential relationship
Date: 2008-03-25 07:55:10
Message-ID: 47E8AFDE.1080905@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-sql
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

Responses

pgsql-sql by date

Next:From: Craig RingerDate: 2008-03-25 13:56:44
Subject: Re: Deferrable NOT NULL REFERENCES ... for two-way referential relationship (SOLVED?)
Previous:From: Erik JonesDate: 2008-03-21 16:21:12
Subject: SOLVED - Re: Dynamic sql and variable record types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group