Thread: Foreign Unique Constraint

Foreign Unique Constraint

From
"Jon Horsman"
Date:
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.


Re: Foreign Unique Constraint

From
"A. Kretschmer"
Date:
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


Re: Foreign Unique Constraint

From
"Jon Horsman"
Date:
> > 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


Re: Foreign Unique Constraint

From
Peter Eisentraut
Date:
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/


Re: Foreign Unique Constraint

From
chester c young
Date:
> 
> 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 


Re: Foreign Unique Constraint

From
"Phillip Smith"
Date:
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


Re: Foreign Unique Constraint

From
"Jon Horsman"
Date:
> 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


Re: Foreign Unique Constraint

From
Peter Eisentraut
Date:
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/


Re: Foreign Unique Constraint

From
"Jon Horsman"
Date:
> 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


Re: Foreign Unique Constraint

From
"Phillip Smith"
Date:
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


Re: Foreign Unique Constraint

From
"Jon Horsman"
Date:
> 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.