Date: Tue, 28 Jan 2003 20:05:15 +1300
To: pgsql-sql@postgresql.org
Subject: Cross-table constraints
Message-ID: <20030128070514.GB7146@diaspora.gen.nz>
Reply-To: rodgerd@diaspora.gen.nz
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.3.28i
From: Rodger Donaldson <rodgerd@diaspora.gen.nz>


The problem: I have two tables, add_queue and sites, on a postgresql
7.2.x database.  add_queue is where items go to be reviewed by a human
before being moved into sites.  One of the things I'd like to do is to
cut down on the amount of work done by humans filtering out dupes and
sundry other problems.

I'd prefer to keep the logic entirely within the DB, rather than on
the client side, to make it consistent across the various interfaces
into add_queue.

Now, adding a UNIQUE constraint on the pk for add_queue weeds out
dupes there.  However, attempting to add a cross-table UNIQUE check
with:

alter table add_queue add constraint add_queue_no_dupe_sites unique
(sites.url);
ERROR:  parser: parse error at or near "."

,,,fails.  Foreign key constraints will only require a match (of one
sort or another) in the foreign table, according to the postgresql 7.2
documentation, and do not have an option to require no match.

Am I missing something obvious (syntax for UNIQUE, for example), or
trying to do something that just doesn't work that way?

-- 
Rodger Donaldson		rodgerd@diaspora.gen.nz
"How do I set my laser printer for stun?"
	-- William Tansil