I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based on a
column called RefId and one called Type, i.e. type 1 is a relationship
to BidItem and type 2 is a relationship to BidAddendum.

Is there any way to specify a foreign key that will allow for the
different types indicating which table the relationship should exist
on? Or do I have to have two separate tables with identical columns
(and remove the type column) ?? I would prefer not to have multiple
identical tables.

On 2 Mar 2005 15:29:16 -0800, pb648174 wrote:

>I have a table called BidItem which has another table called
>BidAddendum related to it by foreign key. I have another table called
>BidFolder which is related to both BidItem and BidAddendum, based on a
>column called RefId and one called Type, i.e. type 1 is a relationship
>to BidItem and type 2 is a relationship to BidAddendum.
>
>Is there any way to specify a foreign key that will allow for the
>different types indicating which table the relationship should exist
>on? Or do I have to have two separate tables with identical columns
>(and remove the type column) ?? I would prefer not to have multiple
>identical tables.

Hi pb648174,

If I understand you correctly, each row in BidFolder is related to
either one row in BidItem or to one row in BidAddendum. Correct so far?

Am I also correct that both BidItem and BidAddendum have RefId as either
PRIMARY KEY or UNIQUE column, so that this column can be used in a
FOREIGN KEY constraint?

The way I would implement this, is to have two RefId columns in the
BidFolder table (of course appropriately named), with a CHECK constraint
to ensure that exactly one of them is populated and the other is NULL:

CREATE TABLE BidFolder
( .......
, .......
, Item_RefId ? DEFAULT NULL -- Replace ? with
, Addendum_RefId ? DEFAULT NULL -- the correct type
, .....
, PRIMARY KEY (....)
, FOREIGN KEY (Item_RefId) REFERENCES BidItem
, FOREIGN KEY (Addendum_RefId) REFERENCES BidAddendum
, CHECK ((Item_RefId IS NULL AND Addendum_RefID IS NOT NULL)
OR (Item_RefId IS NOT NULL AND Addendum_RefID IS NULL))
)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

I am not totally sure i understand what you are trying to do, but
could you do someting like

BidItem
PK RefId
PK Type - Set default to 2
FK BidAddendum_RefId
FK BidAddendum_Type

BidAddendum
PK RefId
PK Type - Set default to 1
FK BidItem_RefId
FK BidItem_Type


BidFolder
PK Whatever
FK RefId
FK Type








"pb648174" <google@webpaul.net> wrote in message news:<1109806156.005319.202280@l41g2000cwc.googleg roups.com>...
> I have a table called BidItem which has another table called
> BidAddendum related to it by foreign key. I have another table called
> BidFolder which is related to both BidItem and BidAddendum, based on a
> column called RefId and one called Type, i.e. type 1 is a relationship
> to BidItem and type 2 is a relationship to BidAddendum.
>
> Is there any way to specify a foreign key that will allow for the
> different types indicating which table the relationship should exist
> on? Or do I have to have two separate tables with identical columns
> (and remove the type column) ?? I would prefer not to have multiple
> identical tables.

No, BidItem and Addendum do not have the type and refid fields. The
Type and RefId columns are only in the BidFolder table and are used to
associate one or more BidFolders with either a BidItem (Type 1) or
BidAddendum (Type 2). I don't htink I can do a compound foreign key
based on the type of 1 or 2, so I'm wondering how I set a foreign key
in this scenario.

Another person suggested using two different columns in the BidFolder
table, which would work but would not be very flexible moving forward,
since if I had another relationship to map, I would have to update all
the tables and stored procs instead of just adding BidFolder entries
with a type of 3.

That works, but if I want to add a third relationship I have to update
the table and all associated stored procedures.. Is there any way to
accomplish it with the existing Type and RefId columns?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On 3 Mar 2005 09:59:52 -0600, P B wrote:

>That works, but if I want to add a third relationship I have to update
>the table and all associated stored procedures.

Hi P B,

Adding a column to a table is not that much work. One ALTER TABLE
statement for the column and one ALTER TABLE statement for the
constraint is all you need. You might also need to run an UPDATE to fill
the new columns with the correct starting data, but you'd need to do
that anyway, regardless of the chosen representation.

Stored procedures that have to do something functional with the third
relationship need to be updated anyway. Stored procedures that don't
need to handle the third relationship don't need to be updated (unless
you use INSERT without column list or SELECT * - but both are bad
practice in a production system anyway).


> Is there any way to
>accomplish it with the existing Type and RefId columns?

Yes, it's pointed out by Linn. Here's a link to a more verbose
explanation of the same principle by Joe Celko:
http://groups-beta.google.com/group/...b18c87e1743165
(beware of possible line wrapping)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

It is a huge amount of work when there is thousands of line of existing
code - I don't want to go and change all the function and stored
procedures referencing these tables - I just want to get foreign key
relationships on the existing tables, so as long as the data structure
changes don't affect current stored procedures, the application code
won't need to be changed either.

The solution you posted a link to is a neat idea, but it is modeling an
"is-a" relationship whereas this is a "has-a" relationship. Let's say I
did the following, to follow the suggestions given so far: (the types
don't exist right now in the Item and Addendum tables, but could be
added easily since they have a default value and will not be referenced
in existing stored procedures)

BidItem
Type 1(default value)
Id
BidAddendum
Type 2(default value)
Id

BidFolder
Type 1 or 2
RefId (references Id in Item or Addendum tables based on Type)

A particular BidItem or BidAddendum will have multiple BidFolder
entries. If I could, I would like to put multiple foreign keys on the
BidFolder table to reference the BidItem and BidAddendum tables, but I
can't do that - I also don't think placing the foreign keys on the
BidItem/Addendum tables will work since the relationship is one to many
from that perspective instead of many to one.

P B (developersdex@webpaul.net) writes:
> That works, but if I want to add a third relationship I have to update
> the table and all associated stored procedures.. Is there any way to
> accomplish it with the existing Type and RefId columns?

If you arrive to this situation - or if you think you can arrive at
this situation - then maybe you need to take a broader look at your
database design.

One alternative is to create a supertable to the parents, and then have
the FK to refer to that table. That table would look like:

CREATE TABLE mothertable (refid ...,
type ....,
PRIMARY KEY(refid),
UNIQUE (refid, type))

The seemingly superfluous UNIQUE constraint, permits you to use an
FK from you lower table.

You could also give up on DRI, and use a trigger instead.

When I have encountered this, I have gone for Hugo's solution in
most cases.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

pb648174 (google@webpaul.net) writes:
> It is a huge amount of work when there is thousands of line of existing
> code - I don't want to go and change all the function and stored
> procedures referencing these tables - I just want to get foreign key
> relationships on the existing tables, so as long as the data structure
> changes don't affect current stored procedures, the application code
> won't need to be changed either.

Thousands of line of code? That's not much. :-)

If you don't want to change the code, but use the tables as they are,
you will have to go for a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

I was hoping to use foreign keys so that replication would be smart
enough to pick up all the relationships.. Will replication correctly
handle triggers automatically or will I need to setup something special
for this scenario?

How would you even accomplish Hugo's suggestion for a "has-a"
relationship? His involves setting the foreign key on the
BidItem/Adendum tables which would be a one to many instead of a many
to one relationship.