Thread: 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.
am Tue, dem 27.03.2007, um 9:21:44 -0400 mailte Jon Horsman folgendes: > 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, I can't find anything about this syntax in the docs... > > 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. I think, you should write a TRIGGER for this. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
> > create table table1 ( > > id SERIAL PRIMARY KEY > > extension UNIQUE, > > I can't find anything about this syntax in the docs... Oops, i'm missing a comma on that first line, should have been: create table table1 ( id SERIAL PRIMARY KEY, extension UNIQUE, ) > I think, you should write a TRIGGER for this. Ok, i'll look into how that works. Anyone have any other input? Thanks, Jon
Jon Horsman wrote: > 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. Make a third table that contains all the extension and add two columns, one referencing table 1 and one referencing table 2, and then add a constraint that only one of the two can be not null. It's not very pretty, but you can use views to make the access simpler. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> > 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. use a pre-insert triggers - one for each table. include something like if exists( select 1 from table2 where extension=new.extension ) then raise exception ... end if; ____________________________________________________________________________________ Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html
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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Jon Horsman
Sent: Tuesday, 27 March 2007 23:22
To: pgsql-sql@postgresql.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
> Perhaps this...? It would work, but depending how many rows are in the > table, it could become incredibly slow. The max rows in either table would be about 1000 or so, which isn't too many. There also should be a hole lot of inserting going on. > ALTER TABLE table1 ADD CHECK (extension NOT IN (SELECT extension FROM > table2)); I guess this is the functionality i was looking for but was hoping there would be a cleaner way to do it. Anyways, thanks to everyone who replied. I'll look into each of the proposed solutions to see what best suits my needs. If anyone has a different solution i'd still like to hear it. Jon
Am Mittwoch, 28. März 2007 00:17 schrieb Phillip Smith: > 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)); Subqueries are not allowed in check constraints. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> Subqueries are not allowed in check constraints. I hadn't tried this yet but i'm sure i would have figured this out quickly then =) Thanks, Jon
Is that an 8.2 thing? I'm sure I've done it before, probably in 8.1 Maybe it was a trigger I did it in - I can't remember what I had for breakfast, let alone a slow sub-query I did months ago :P Cheers, ~p -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Peter Eisentraut Sent: Wednesday, 28 March 2007 19:08 To: pgsql-sql@postgresql.org Cc: Phillip Smith; 'Jon Horsman' Subject: Re: [SQL] Foreign Unique Constraint Am Mittwoch, 28. März 2007 00:17 schrieb Phillip Smith: > 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)); Subqueries are not allowed in check constraints. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster *******************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
> use a pre-insert triggers - one for each table. include something like I ended up going this route and it seems to work. Thanks for the help from all. I figured i'd post the solution to the list so it shows up when googled. Also, if my solution can be simplfied i'd appreciate knowing how. This would be the trigger for table1 in my example. CREATE FUNCTION function_name() RETURNS trigger AS ' DECLARE result RECORD; BEGIN SELECT INTO result * FROM table2 WHERE extension=NEW.extension; IF FOUND THEN RAISEEXCEPTION ''The extension % is already in use'', NEW.extension; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER function_name BEFORE INSERT OR UPDATE ON table1 FOR EACH ROW EXECUTE PROCEDURE function_name(); Again, thanks for the help. Jon.