|
|
|
|
|
|
| Author |
Message |
MargaretGillon@chromalloy
*nix forums beginner
Joined: 24 Oct 2005
Posts: 20
|
 Posted: Wed Jul 19, 2006 11:26 pm Post subject:
Re: Constraint for two fields unique any order
|
|
|
| Quote: | On Wed, 2006-07-19 at 09:01 -0700, MargaretGillon@chromalloy.com wrote:
I have a junction table that is recording relationships between two
records in another table. Is there a way that I can create a
constraint so that the values are not repeated in any order? I want to
make sure that rows such as 2 and 4 in the example below cannot
happen. This is a very small table that is meta data for an
application. It is only 41 rows now and probably won't grow beyond 200
rows. I am on Postgresql ver 7.3.4 .
id fkey1 fkey2
1 3 4
2 10 4
3 2 7
4 4 10
5 15 8
I can think of two solutions with slightly different semantics.
1) If the directionality of the association is immaterial, then the
easiest approach is to impose the convention that rows always satisfy
fkey1<fkey2 and then create a unique index on (fkey1,fkey2). At a
minimum, you should have a check constraint verify this condition. You
might consider writing a trigger for insert and update to swap fkey1 and
fkey2 when necessary.
For example:
create table jx1 (
id serial primary key,
fkey1 integer not null,
fkey2 integer not null,
constraint jx1_invalid_key_order check (fkey1<fkey2),
constraint jx1_unique_association unique (fkey1,fkey2)
);
2) If you care about directionality and really seek to preclude
symmetric relationships (as in a family tree), then create a unique
index on the reordered pairs, like this:
create table jx2 (
id serial primary key,
fkey1 integer not null,
fkey2 integer not null
);
create or replace function jx_reorder(integer,integer) returns text
strict immutable language sql as
'SELECT CASE WHEN $1<$2 THEN $1||''-''||$2 ELSE $2||''-''||$1 END';
create unique index jx2_no_symmetric_reln on jx2
(jx_reorder(fkey1,fkey2));
These should work fine on 7.3.4, but I didn't verify that. You should
consider upgrading.
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
|
Thanks for the suggestions Reece. Some of the pairs are aleady being used
in code so I don't know if I can reverse the order to create the
fkey1<fkey2 condition. I'm going to check my code tomorrow and see if I
can rearrange the keys without too much impact on the software. I also
need to verify that I'll never have a pair where fkey1 = fkey2.
Margaret Gillon |
|
| Back to top |
|
 |
Reece Hart
*nix forums beginner
Joined: 13 Jul 2006
Posts: 4
|
| Posted: Wed Jul 19, 2006 5:13 pm Post subject:
Re: Constraint for two fields unique any order
|
|
|
On Wed, 2006-07-19 at 09:01 -0700, MargaretGillon@chromalloy.com wrote:
| Quote: | I have a junction table that is recording relationships between two
records in another table. Is there a way that I can create a
constraint so that the values are not repeated in any order? I want to
make sure that rows such as 2 and 4 in the example below cannot
happen. This is a very small table that is meta data for an
application. It is only 41 rows now and probably won't grow beyond 200
rows. I am on Postgresql ver 7.3.4 .
id fkey1 fkey2
1 3 4
2 10 4
3 2 7
4 4 10
5 15 8
|
I can think of two solutions with slightly different semantics.
1) If the directionality of the association is immaterial, then the
easiest approach is to impose the convention that rows always satisfy
fkey1<fkey2 and then create a unique index on (fkey1,fkey2). At a
minimum, you should have a check constraint verify this condition. You
might consider writing a trigger for insert and update to swap fkey1 and
fkey2 when necessary.
For example:
create table jx1 (
id serial primary key,
fkey1 integer not null,
fkey2 integer not null,
constraint jx1_invalid_key_order check (fkey1<fkey2),
constraint jx1_unique_association unique (fkey1,fkey2)
);
2) If you care about directionality and really seek to preclude
symmetric relationships (as in a family tree), then create a unique
index on the reordered pairs, like this:
create table jx2 (
id serial primary key,
fkey1 integer not null,
fkey2 integer not null
);
create or replace function jx_reorder(integer,integer) returns text
strict immutable language sql as
'SELECT CASE WHEN $1<$2 THEN $1||''-''||$2 ELSE $2||''-''||$1 END';
create unique index jx2_no_symmetric_reln on jx2 (jx_reorder(fkey1,fkey2));
These should work fine on 7.3.4, but I didn't verify that. You should
consider upgrading.
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq |
|
| Back to top |
|
|
Bob Dowling
*nix forums beginner
Joined: 19 Jul 2006
Posts: 1
|
| Posted: Wed Jul 19, 2006 5:09 pm Post subject:
Re: Constraint for two fields unique any order
|
|
|
On Wed, 19 Jul 2006 MargaretGillon@chromalloy.com wrote:
| Quote: | I have a junction table that is recording relationships between two
records in another table. Is there a way that I can create a constraint so
that the values are not repeated in any order?
|
I think the following does what you need, though my "foo_table" won't be
the same as your junction table. If you can't put a UNIQUE constraint in
your table, add to the PERFORM in the function.
CREATE TABLE foo_table
(
id SERIAL PRIMARY KEY,
fkey1 INTEGER NOT NULL,
fkey2 INTEGER NOT NULL,
UNIQUE(fkey1, fkey2)
);
CREATE FUNCTION other_way_test()
RETURNS TRIGGER
AS $$
BEGIN
PERFORM id FROM foo_table WHERE foo_table.fkey1=NEW.fkey2 AND foo_table.fkey2=NEW.fkey1;
IF FOUND
THEN
RAISE NOTICE 'pair of numbers violates reverse order uniqueness';
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END
$$
LANGUAGE 'plpgsql'
;
CREATE TRIGGER other_way_test
BEFORE INSERT
ON foo_table
FOR EACH ROW
EXECUTE PROCEDURE other_way_test()
;
--
Bob Dowling: Head of Unix Systems Division, University Computing Service
rjd4@cam.ac.uk New Museums Site, Pembroke Street, Cambridge. CB2 3QH
01223 334710 http://www-uxsup.csx.cam.ac.uk/~rjd4/
--- Those who do not learn from Dilbert are doomed to repeat it. ---
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq |
|
| Back to top |
|
|
MargaretGillon@chromalloy
*nix forums beginner
Joined: 24 Oct 2005
Posts: 20
|
| Posted: Wed Jul 19, 2006 4:42 pm Post subject:
Re: Constraint for two fields unique any order
|
|
|
Hi Richard and list,
Thanks for the reply. On the constraint what I am trying to create is a
constraint that would not let me create a row with fkey1 = 4 and fkey2 =
10 if I already had a row that had fkey1 = 10 and fkey2 = 4. The
constraint needs to check the combination of values in any order.
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 |
|
| Back to top |
|
|
Richard Broersma Jr
*nix forums beginner
Joined: 13 Mar 2006
Posts: 44
|
| Posted: Wed Jul 19, 2006 4:25 pm Post subject:
Re: Constraint for two fields unique any order
|
|
|
| Quote: | I have a junction table that is recording relationships between two
records in another table. Is there a way that I can create a constraint so
that the values are not repeated in any order? I want to make sure that
rows such as 2 and 4 in the example below cannot happen. This is a very
small table that is meta data for an application. It is only 41 rows now
and probably won't grow beyond 200 rows. I am on Postgresql ver 7.3.4 .
id fkey1 fkey2
1 3 4
2 10 4
3 2 7
4 4 10
5 15 8
|
CREATE TABLE junction
(
);
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly |
|
| Back to top |
|
|
MargaretGillon@chromalloy
*nix forums beginner
Joined: 24 Oct 2005
Posts: 20
|
| Posted: Wed Jul 19, 2006 4:01 pm Post subject:
Constraint for two fields unique any order
|
|
|
I have a junction table that is recording relationships between two
records in another table. Is there a way that I can create a constraint so
that the values are not repeated in any order? I want to make sure that
rows such as 2 and 4 in the example below cannot happen. This is a very
small table that is meta data for an application. It is only 41 rows now
and probably won't grow beyond 200 rows. I am on Postgresql ver 7.3.4 .
id fkey1 fkey2
1 3 4
2 10 4
3 2 7
4 4 10
5 15 8
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Dec 04, 2008 4:18 am | All times are GMT
|
|
Free Ringtones | AdSense Optimization Tutorials | Mortgages | MPAA | Mortgage
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
PostgreSQL
