Thread: Cross-table constraints
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
On Tue, 28 Jan 2003, Rodger Donaldson wrote: > 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? I'm not sure what a unique constraint on sites.url on table add_queue is supposed to mean precisely. You can probably come pretty close by making a set of triggers on both tables however.
On Wed, 29 Jan 2003, Rodger Donaldson wrote: > > On Tue, 28 Jan 2003, Rodger Donaldson wrote: > > > > > 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? > > > > I'm not sure what a unique constraint on sites.url on table add_queue > > is supposed to mean precisely. > > Sorry; to clarify, it's table.column notation. I meant to signify what's the behavior? Is it different from a unique constraint on sites(url)? If so, how?
> On Tue, 28 Jan 2003, Rodger Donaldson wrote: > > > 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? > > I'm not sure what a unique constraint on sites.url on table add_queue > is supposed to mean precisely. Sorry; to clarify, it's table.column notation. > You can probably come pretty close by making a set of triggers on > both tables however. That's what I feared 8). -- Rodger Donaldson rodgerd@diaspora.gen.nz
On Tue, Jan 28, 2003 at 20:05:15 +1300, Rodger Donaldson <rodgerd@diaspora.gen.nz> wrote: > > 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. Create a third table with three id columns. The first is the primary key and the other two tables should reference it. The second and third should allow for nulls and each reference the id field in one of the other two tables. You also need a table constraint that forces exactly one of these two fields to be null. And two more constraints to make sure they match the primary key when they aren't null. This should make sure each id is in exactly one of the two original tables and that any id in the two original tables is in the new table.