pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign Unique Constraint
11+ messages / 5 participants
[nested] [flat]
* Foreign Unique Constraint
@ 2007-03-27 13:21 Jon Horsman <horshaq@gmail.com>
2007-03-27 13:36 ` Re: Foreign Unique Constraint A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-03-27 15:58 ` Re: Foreign Unique Constraint Peter Eisentraut <peter_e@gmx.net>
2007-03-27 17:35 ` Re: Foreign Unique Constraint chester c young <chestercyoung@yahoo.com>
2007-03-27 22:17 ` Re: Foreign Unique Constraint Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 4 replies; 11+ messages in thread
From: Jon Horsman @ 2007-03-27 13:21 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
@ 2007-03-27 13:36 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-03-27 14:27 ` Re: Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: A. Kretschmer @ 2007-03-27 13:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-27 13:36 ` Re: Foreign Unique Constraint A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-03-27 14:27 ` Jon Horsman <horshaq@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jon Horsman @ 2007-03-27 14:27 UTC (permalink / raw)
To: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
@ 2007-03-27 15:58 ` Peter Eisentraut <peter_e@gmx.net>
3 siblings, 0 replies; 11+ messages in thread
From: Peter Eisentraut @ 2007-03-27 15:58 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jon Horsman <horshaq@gmail.com>
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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
@ 2007-03-27 17:35 ` chester c young <chestercyoung@yahoo.com>
2007-03-29 21:23 ` Re: Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: chester c young @ 2007-03-27 17:35 UTC (permalink / raw)
To: Jon Horsman <horshaq@gmail.com>; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-27 17:35 ` Re: Foreign Unique Constraint chester c young <chestercyoung@yahoo.com>
@ 2007-03-29 21:23 ` Jon Horsman <horshaq@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jon Horsman @ 2007-03-29 21:23 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
> 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
RAISE EXCEPTION ''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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
@ 2007-03-27 22:17 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-03-28 03:51 ` Re: Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-28 09:08 ` Re: Foreign Unique Constraint Peter Eisentraut <peter_e@gmx.net>
3 siblings, 2 replies; 11+ messages in thread
From: Phillip Smith @ 2007-03-27 22:17 UTC (permalink / raw)
To: 'Jon Horsman' <horshaq@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-27 22:17 ` Re: Foreign Unique Constraint Phillip Smith <phillip.smith@weatherbeeta.com.au>
@ 2007-03-28 03:51 ` Jon Horsman <horshaq@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jon Horsman @ 2007-03-28 03:51 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-27 22:17 ` Re: Foreign Unique Constraint Phillip Smith <phillip.smith@weatherbeeta.com.au>
@ 2007-03-28 09:08 ` Peter Eisentraut <peter_e@gmx.net>
2007-03-28 13:28 ` Re: Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-28 22:21 ` Re: Foreign Unique Constraint Phillip Smith <phillip.smith@weatherbeeta.com.au>
1 sibling, 2 replies; 11+ messages in thread
From: Peter Eisentraut @ 2007-03-28 09:08 UTC (permalink / raw)
To: pgsql-sql; +Cc: Phillip Smith <phillip.smith@weatherbeeta.com.au>; 'Jon Horsman' <horshaq@gmail.com>
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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-27 22:17 ` Re: Foreign Unique Constraint Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-03-28 09:08 ` Re: Foreign Unique Constraint Peter Eisentraut <peter_e@gmx.net>
@ 2007-03-28 13:28 ` Jon Horsman <horshaq@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jon Horsman @ 2007-03-28 13:28 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql@postgresql.org, "Phillip Smith" <phillip.smith@weatherbeeta.com.au>
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Foreign Unique Constraint
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-27 22:17 ` Re: Foreign Unique Constraint Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-03-28 09:08 ` Re: Foreign Unique Constraint Peter Eisentraut <peter_e@gmx.net>
@ 2007-03-28 22:21 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
1 sibling, 0 replies; 11+ messages in thread
From: Phillip Smith @ 2007-03-28 22:21 UTC (permalink / raw)
To: 'Peter Eisentraut' <peter_e@gmx.net>; pgsql-sql; +Cc: 'Jon Horsman' <horshaq@gmail.com>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-03-29 21:23 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-27 13:21 Foreign Unique Constraint Jon Horsman <horshaq@gmail.com>
2007-03-27 13:36 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-03-27 14:27 ` Jon Horsman <horshaq@gmail.com>
2007-03-27 15:58 ` Peter Eisentraut <peter_e@gmx.net>
2007-03-27 17:35 ` chester c young <chestercyoung@yahoo.com>
2007-03-29 21:23 ` Jon Horsman <horshaq@gmail.com>
2007-03-27 22:17 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-03-28 03:51 ` Jon Horsman <horshaq@gmail.com>
2007-03-28 09:08 ` Peter Eisentraut <peter_e@gmx.net>
2007-03-28 13:28 ` Jon Horsman <horshaq@gmail.com>
2007-03-28 22:21 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox