Thread: One or Both Foregin Keys
I am working on a design where a location for something can have:
Fully qualified address with even building names, room numbers, and
booth numbers.
**-OR-**
GPS location
**-OR-**
Both
Other than triggers, is there a way to enforce this using tables and
primary keys?
I imagine an address table, and then
either a some sort of intermediate table, in a one to many
relationship, GPS to Addresses,
or the correct columns in the address table.
Either way, I don't see a normal way to enforce EITHER or BOTH without a
trigger.
Thanks in Advance.
Dennis Gearon <gearond@fireserve.net> writes:
> I am working on a design where a location for something can have:
> Fully qualified address with even building names, room numbers, and
> booth numbers.
> **-OR-**
> GPS location
> **-OR-**
> Both
> Other than triggers, is there a way to enforce this using tables and
> primary keys?
Put foreign key constraints on the address and GPS loc (so that they
must be either valid or NULL) and then add a CHECK constraint saying
they can't both be NULL.
regards, tom lane
Hi, I have a key structure like so: a c b +----------+ +---------+ +----------+ | PriKey1 |--->| ForKey |<---| PriKey2 | | | | Flag | | | +----------+ +---------+ +----------+ ...where c.ForKey is a value from PriKey1 OR PriKey2, which are different values. All fields have the same data type; a.PriKey1 and b.PriKey2 are sequences. How does one enfore referential integrity in this structure so that c.ForKey references a.PriKeya when Flag is True or references b.PriKey2 when Flag is False? Looked pretty hard through the lists and on Google last night with no luck :( -Ryan Riehle http://www.buildways.com KEYWORDS: one foreign key, multiple primary keys, multiple tables, generalization, superclass, subclass, constraint
Use CHECK when you create the table. Ryan Riehle wrote: > Hi, > > I have a key structure like so: > > a c b > +----------+ +---------+ +----------+ > | PriKey1 |--->| ForKey |<---| PriKey2 | > | | | Flag | | | > +----------+ +---------+ +----------+ > > ...where c.ForKey is a value from PriKey1 OR PriKey2, which are different > values. All fields have the same data type; a.PriKey1 and b.PriKey2 are > sequences. How does one enfore referential integrity in this structure so > that c.ForKey references a.PriKeya when Flag is True or references b.PriKey2 > when Flag is False? Looked pretty hard through the lists and on Google last > night with no luck :( > > -Ryan Riehle > http://www.buildways.com > > KEYWORDS: one foreign key, multiple primary keys, multiple tables, > generalization, superclass, subclass, constraint > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Sat, May 01, 2004 at 14:51:14 -0400, Ryan Riehle <rkr@buildways.com> wrote: > Hi, > > I have a key structure like so: > > a c b > +----------+ +---------+ +----------+ > | PriKey1 |--->| ForKey |<---| PriKey2 | > | | | Flag | | | > +----------+ +---------+ +----------+ > > ...where c.ForKey is a value from PriKey1 OR PriKey2, which are different > values. All fields have the same data type; a.PriKey1 and b.PriKey2 are > sequences. How does one enfore referential integrity in this structure so > that c.ForKey references a.PriKeya when Flag is True or references b.PriKey2 > when Flag is False? Looked pretty hard through the lists and on Google last > night with no luck :( Your diagram seems to indicate something other than what you said. If c is supposed to reference a or b from one field I don't think you will be able to do that without writing your own triggers. If you can use two fields you call use NULL in the one that isn't active and use constraints to make sure exactly the one that is suppused to be nonNULL is. If you really have a and b pointing to c, then duplicate flag and a and b and use a combined foreign key reference of the primary key and the flag into c. Use constraints to make sure the flag field is always true for a and always false for b.
Sorry; Arrows are going the wrong way (seems like that caused some confusion). Corrected: a c b +----------+ +---------+ +----------+ | PriKey1 |<---| ForKey |--->| PriKey2 | | | | Flag | | | +----------+ +---------+ +----------+ Kind Regards, -Ryan Riehle http://www.buildways.com -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bruno Wolff III Sent: Saturday, May 01, 2004 4:18 PM To: Ryan Riehle Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 1 foreign key to 2 different tables? On Sat, May 01, 2004 at 14:51:14 -0400, Ryan Riehle <rkr@buildways.com> wrote: > Hi, > > I have a key structure like so: > > a c b > +----------+ +---------+ +----------+ > | PriKey1 |--->| ForKey |<---| PriKey2 | > | | | Flag | | | > +----------+ +---------+ +----------+ > > ...where c.ForKey is a value from PriKey1 OR PriKey2, which are > different values. All fields have the same data type; a.PriKey1 and > b.PriKey2 are sequences. How does one enfore referential integrity in > this structure so that c.ForKey references a.PriKeya when Flag is True > or references b.PriKey2 when Flag is False? Looked pretty hard through > the lists and on Google last night with no luck :( Your diagram seems to indicate something other than what you said. If c is supposed to reference a or b from one field I don't think you will be able to do that without writing your own triggers. If you can use two fields you call use NULL in the one that isn't active and use constraints to make sure exactly the one that is suppused to be nonNULL is. If you really have a and b pointing to c, then duplicate flag and a and b and use a combined foreign key reference of the primary key and the flag into c. Use constraints to make sure the flag field is always true for a and always false for b. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
For what I am reading now it looks like this is an opportunity to use CREATE ASSERTION, but this functionality appears to be unstable so far and is not recommended for production environments. Is this correct? Otherwise, can someone post an example of implementing a check constraint or trigger since I have not created a check onstraint that is above common complexity and and have never tried a trigger. Kind Regards, -Ryan Riehle http://www.buildways.com -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ryan Riehle Sent: Saturday, May 01, 2004 4:57 PM To: 'Bruno Wolff III' Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 1 foreign key to 2 different tables? Sorry; Arrows are going the wrong way (seems like that caused some confusion). Corrected: a c b +----------+ +---------+ +----------+ | PriKey1 |<---| ForKey |--->| PriKey2 | | | | Flag | | | +----------+ +---------+ +----------+ Kind Regards, -Ryan Riehle http://www.buildways.com -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bruno Wolff III Sent: Saturday, May 01, 2004 4:18 PM To: Ryan Riehle Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 1 foreign key to 2 different tables? On Sat, May 01, 2004 at 14:51:14 -0400, Ryan Riehle <rkr@buildways.com> wrote: > Hi, > > I have a key structure like so: > > a c b > +----------+ +---------+ +----------+ > | PriKey1 |--->| ForKey |<---| PriKey2 | > | | | Flag | | | > +----------+ +---------+ +----------+ > > ...where c.ForKey is a value from PriKey1 OR PriKey2, which are > different values. All fields have the same data type; a.PriKey1 and > b.PriKey2 are sequences. How does one enfore referential integrity in > this structure so that c.ForKey references a.PriKeya when Flag is True > or references b.PriKey2 when Flag is False? Looked pretty hard through > the lists and on Google last night with no luck :( Your diagram seems to indicate something other than what you said. If c is supposed to reference a or b from one field I don't think you will be able to do that without writing your own triggers. If you can use two fields you call use NULL in the one that isn't active and use constraints to make sure exactly the one that is suppused to be nonNULL is. If you really have a and b pointing to c, then duplicate flag and a and b and use a combined foreign key reference of the primary key and the flag into c. Use constraints to make sure the flag field is always true for a and always false for b. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
On Sat, May 01, 2004 at 18:09:34 -0400, Ryan Riehle <rkr@buildways.com> wrote: > For what I am reading now it looks like this is an opportunity to use CREATE > ASSERTION, but this functionality appears to be unstable so far and is not > recommended for production environments. Is this correct? Otherwise, can > someone post an example of implementing a check constraint or trigger since > I have not created a check onstraint that is above common complexity and and > have never tried a trigger. The simplest way to do this is probably be to use two separate fields to make the references and make sure exactly one of them is nonnull. You also might want to rethink your design. That you want to do this suggests that there is something odd about the schema design you have come up with.
Thanks for your input. Yes, there is a lot more to this part of the schema. The current stucture makes the most sense to us so far, after lots of thinking. If you are interested I can offer you more details about the structure, but for now I am looking for how to implement this type of constraint with a trigger or another method - if there is a better way. -Ryan Riehle http://www.buildways.com -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bruno Wolff III Sent: Saturday, May 01, 2004 9:40 PM To: Ryan Riehle Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 1 foreign key to 2 different tables? On Sat, May 01, 2004 at 18:09:34 -0400, Ryan Riehle <rkr@buildways.com> wrote: > For what I am reading now it looks like this is an opportunity to use > CREATE ASSERTION, but this functionality appears to be unstable so far and is not > recommended for production environments. Is this correct? Otherwise, can > someone post an example of implementing a check constraint or trigger > since I have not created a check onstraint that is above common > complexity and and have never tried a trigger. The simplest way to do this is probably be to use two separate fields to make the references and make sure exactly one of them is nonnull. You also might want to rethink your design. That you want to do this suggests that there is something odd about the schema design you have come up with. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
On Sun, May 02, 2004 at 03:51:44 -0400, Ryan Riehle <rkr@buildways.com> wrote: > Thanks for your input. Yes, there is a lot more to this part of the schema. > The current stucture makes the most sense to us so far, after lots of > thinking. If you are interested I can offer you more details about the > structure, but for now I am looking for how to implement this type of > constraint with a trigger or another method - if there is a better way. So far I haven't seen a good case for why there needs to only be one pointer instead of two. When you are doing joins you are going to know which one you want to join on. They are internal keys so you shouldn't need to print them out directly. If you want to display something that comes out of one joined table or the other, you are going to have to join both a and b to c using some sort of outer joins. You can use coalesce to get the information form the appropiate joined table. You will still have to do things like this even if you share the pointer field c, but it will be easier to set up the constraints if there are two separate fields.
I don't know if this will work in your situation, but you might look into having table A and table B inherit from a common base table (where the column referenced by the foreign key in C is defined). Tim On May 2, 2004, at 2:51 AM, Ryan Riehle wrote: > Thanks for your input. Yes, there is a lot more to this part of the > schema. > The current stucture makes the most sense to us so far, after lots of > thinking. If you are interested I can offer you more details about the > structure, but for now I am looking for how to implement this type of > constraint with a trigger or another method - if there is a better way. > > -Ryan Riehle > http://www.buildways.com > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bruno Wolff > III > Sent: Saturday, May 01, 2004 9:40 PM > To: Ryan Riehle > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] 1 foreign key to 2 different tables? > > > On Sat, May 01, 2004 at 18:09:34 -0400, > Ryan Riehle <rkr@buildways.com> wrote: >> For what I am reading now it looks like this is an opportunity to use >> CREATE ASSERTION, but this functionality appears to be unstable so >> far and > is not >> recommended for production environments. Is this correct? >> Otherwise, > can >> someone post an example of implementing a check constraint or trigger >> since I have not created a check onstraint that is above common >> complexity and and have never tried a trigger. > > The simplest way to do this is probably be to use two separate fields > to > make the references and make sure exactly one of them is nonnull. You > also > might want to rethink your design. That you want to do this suggests > that > there is something odd about the schema design you have come up with. > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend >