Re: Foreign Unique Constraint

Perhaps this...? It would work, but depending how many rows are in the
table, it could become incredibly slow.

ALTER TABLE table1 ADD CHECK (extension NOT IN (SELECT extension FROM
table2));

And the converse for table2:
ALTER TABLE table2 ADD CHECK (extension NOT IN (SELECT extension FROM
table1));

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Jon Horsman
Sent: Tuesday, 27 March 2007 23:22
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] Foreign Unique Constraint

I was wondering if someone could help point me in the right direction
w.r.t. foreign unique constraints. I'm working on a legacy database
and have a new requirement and am not sure how to do it.

I have something like this

create table table1 (
id SERIAL PRIMARY KEY
extension UNIQUE,
<other fields>
)

create table table2 (
id SERIAL PRIMARY KEY
extension UNIQUE,
<different fields>
)

Basically table 1 and table 2 both have the concept of an extension
that must be unique but the rest of the info in the tables are
different. I need to ensure that if i add an entry to table 1 with
extension 1000 that it will fail if there is already an entry in
table2 with the same extension.

Essentially i need to do something like the following but i get errors
saying this can't be done.

alter table table1 add check (extension <> table2.extension);

It should be noted i am using pg version 7.4.13 and this can't change.

Thanks for any help you can offer.

Jon.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments