Composite Key with fields in two tables?

Question

Hello all,

I've found myself in a bit of a confusing state... I have the following relation setup:

This is a setup I've only been recently shown (by someone here on this form) and cant quite wrap my head around how to apply an integrity rule to this table (if at all possible in just the DB structure).

What I want to do is ensure that any given parent does not have children with the same name. Ideally I would create a composite key using Parent_ID and LineItem_Name, thus adhering to my desired rule. But seeing as the needed columns are in separate tables (and I only want to enforce this rule on the sub-type anyway) I cannot wrap my head around doing this in the database itself... can this be done? or do I need to rearrange my structure?

Answer 1

You can't enforce RI in this way: a composite key refers to fields in one record, not to the same field in two different records. I'd suggest enforcing this data rule using the BEFORE_UPDATE event of whatever form is used to enter the names.

Answer 2

Assuming that LineItem_Name is a candidate key of Line_Items then I think you just need a table-level ValidationRule in WBS_LineItems of:

    [LineItem_ID] <> [Parent_ID]

i.e. no row can reference itself.

Answer 3

That is a rule I will have to abide by as well, but it is not quite what I am talking about here.

What I want is to restrict sibling names to be unique in that generation for a specific parent. I.E. a child's direct sibling needs a unique name from each direct sibling.

Answer 4

Perhaps I'm missing something, but still assuming that LineItem_Name is a candidate key of Line_Items, and consequently a determinant of both LineItem_ID and Parent_ID,  I cannot see how that constraint could be violated in the current model.  Can you provide some sample rows from WBS_LineItems which would violate the constraint?