Two parent tables related to the same Child table

[Spatz]

Hello all,

I have a problem that I'm not sure can be solved with a typical
relationship in a DataSet.

I have 3 tables in a dataset. 2 parents and a child. I have set up
relationships between the parent tables and the child tables. The way
I have set up the relationships is like this. ParentTables have an
identity column, lets say ParentTable1ID or ParentTable2ID, and a
'SourcetypeID' column. The 'SourceTypeID' column is an integer
(either 1 or 2). I.E. In Parenttable1 all rows have 1 in the column
SourceTypeID and in Parenttable2 all rows have 2 in the column
SourceTypeID.

In the child table there is a column of ParentUniqueID and
SourceTypeID. ParentUniqueID column maps to the parentID based on the
SourceTypeID column. This way I can have rows in the childtable are
associated with a row in either Parenttable1 or ParentTable2 based on
the SourceTypeID column. So I have set up a relationship with both the
ParentUniqueID and SourceTypeID columns (a multicolumn relationship).
To put it another way if there is a row in the child table with
ParentUniqueID set to 3 and SourceTypeID set to 1, the relationship
dictates, the child row is associated to ParentTable1 with the
ParentTableID of 3.

Here is the problem. Becuase rows in the child table can have a
parent in either parent table, I can not enable the constraint when
creating the two relationships. This is because every row in a child
table will be related to a single row in EITHER ParentTable1 or
ParentTable2. Having said that, from what I understand, if you don't
have a FK constraint on the child table you can not get cascading
changes between the parent and child tables.

So when I am updating the parents identity column it is not updating
the childs 'ParentUniqueID' column to reflect the change.

Is it possible to cacade changes in a dataset with relationships set
up in this manner? If not, what is a good technique for updating
children rows data when a parent tables row updates?

Thanks for all responses in advance!

Tom

 

[Colin Young]

It's much easier if you include the DDL for your tables rather than trying
to describe them.

If I understand correctly, you want a table that has a foreign key to either
table A or table B, but not both? If that is indeed the case, the solution
is to split the child table into 2 tables, one child for each of the parent
tables. I can't think of a good reason for designing your schema in the
manner you've described.

Colin