One or Both Foregin Keys

[Dennis Gearon]

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: One or Both Foregin Keys

Dennis Gearon <gearond@firese rve.net> writes:[color=blue]
> 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[/color]
[color=blue]
> Other than triggers, is there a way to enforce this using tables and
> primary keys?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Re: One or Both Foregin Keys

Dennis Gearon <gearond@firese rve.net> writes:[color=blue]
> 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[/color]
[color=blue]
> Other than triggers, is there a way to enforce this using tables and
> primary keys?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Ryan Riehle]

1 foreign key to 2 different tables?

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@postg resql.org

[Ryan Riehle]

1 foreign key to 2 different tables?

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@postg resql.org

[Jean-Luc Lachance]

Re: 1 foreign key to 2 different tables?

Use CHECK when you create the table.



Ryan Riehle wrote:
[color=blue]
> 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@postg resql.org
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jean-Luc Lachance]

Re: 1 foreign key to 2 different tables?

Use CHECK when you create the table.



Ryan Riehle wrote:
[color=blue]
> 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@postg resql.org
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bruno Wolff III]

Re: 1 foreign key to 2 different tables?

On Sat, May 01, 2004 at 14:51:14 -0400,
Ryan Riehle <rkr@buildways. com> wrote:[color=blue]
> 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 :([/color]

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

[Bruno Wolff III]

Re: 1 foreign key to 2 different tables?

On Sat, May 01, 2004 at 14:51:14 -0400,
Ryan Riehle <rkr@buildways. com> wrote:[color=blue]
> 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 :([/color]

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

[Ryan Riehle]

Re: 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@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Bruno Wolff III
Sent: Saturday, May 01, 2004 4:18 PM
To: Ryan Riehle
Cc: pgsql-general@postgre sql.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:[color=blue]
> 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 :([/color]

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

[Ryan Riehle]

Re: 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@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Bruno Wolff III
Sent: Saturday, May 01, 2004 4:18 PM
To: Ryan Riehle
Cc: pgsql-general@postgre sql.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:[color=blue]
> 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 :([/color]

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

[Ryan Riehle]

Re: 1 foreign key to 2 different tables?

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@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Ryan Riehle
Sent: Saturday, May 01, 2004 4:57 PM
To: 'Bruno Wolff III'
Cc: pgsql-general@postgre sql.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@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Bruno Wolff III
Sent: Saturday, May 01, 2004 4:18 PM
To: Ryan Riehle
Cc: pgsql-general@postgre sql.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:[color=blue]
> 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 :([/color]

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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Ryan Riehle]

Re: 1 foreign key to 2 different tables?

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@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Ryan Riehle
Sent: Saturday, May 01, 2004 4:57 PM
To: 'Bruno Wolff III'
Cc: pgsql-general@postgre sql.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@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Bruno Wolff III
Sent: Saturday, May 01, 2004 4:18 PM
To: Ryan Riehle
Cc: pgsql-general@postgre sql.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:[color=blue]
> 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 :([/color]

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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Bruno Wolff III]

Re: 1 foreign key to 2 different tables?

On Sat, May 01, 2004 at 18:09:34 -0400,
Ryan Riehle <rkr@buildways. com> wrote:[color=blue]
> 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.[/color]

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

[Bruno Wolff III]

Re: 1 foreign key to 2 different tables?

On Sat, May 01, 2004 at 18:09:34 -0400,
Ryan Riehle <rkr@buildways. com> wrote:[color=blue]
> 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.[/color]

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