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

Mayhaps it is I that does not understand what you are saying....

Could you please define Candidate Key for me? Is it similar to a Composite Key in nature?

As far as my understanding of my setup goes there is nothing stopping LineItems from having duplicate names (And this needs to be supported as there are many duplicated names at varying generations). But, I do not want Siblings of the same parent to share a Name...

Concrete example from a temp table creating the generations of WBS_LineItems:

As you can see, the Names "Data" and "Training" are repeated at separate generations. I want to restrict these names from repeating within the same generation under the same parent.

Answer 2

A candidate key is a column or non-trivial combination of columns whose values must be distinct in a table.  Consequently LineItem_Name (never use Name as a column name – it is a reserved keyword!) is not a candidate key.  I think you should be able to enforce the constraint as a CHECK CONSTRAINT, however.  In essence a CHECK CONSTRAINT is an SQL statement which must always evaluate to TRUE.  It can consequently encompass a number of related tables.  It is implemented as an ALTER TABLE statement, but unlike a normal ALTER TABLE statement to amend a table definition, in Access this cannot be done in SQL view, so has to be done in code, usually in the Immediate window.  The same is true to DROP a CONSTRAINT.

I would really need some data from both tables with which to test the constraint before I can give you the necessary code.  I have a rough idea in my head what form the SQL statement will take, but will need to test it.  You can supply the data, which does not have to be extensive, merely representative, in one of two ways:

1.  You can post rows from each table here as comma or tab delimited text, with the column names as the first line, terminating each line with a carriage return, which can be copied and imported into a table.

2.  You can post an Access file to a site such as Microsoft's OneDrive and post a link to it in a reply here, so that it can be downloaded.

I can't promise you a quick reply due to the current constraints on my time due to the daily medical treatment I'm receiving, but I'll attend to it as soon as possible.

BTW, on the basis of your screenshot, WBS_Num does appear to be a candidate key.  If so it must be indexed uniquely (no duplicates) to designate it as such.

Answer 3

I'll throw the first 20 lines at you for each table (they line up just fine for now, but there is a total of 820 or so entries in LineItems and I don't think you need that much, let me know if you need more).

LineItems Table:

"LineItem_ID","LineItem_Name","Sibling_Pos"

1,"Strategic Missile Systems",1

2,"Strategic Missile Systems Integration, Assembly, Test and Checkout",1

3,"Air Vehicle Equipment (AVE)/Flight Vehicle Equipment (FVE)",2

4,"AVE/FVE SEIT/PM and Support Equipment",1

5,"Integration, Assembly, Test, and Checkout",1

6,"Program Management",2

7,"Systems Engineering",3

8,"Training",4

9,"Data",5

10,"Operational/Site Activation",6

11,"Initial Spares/Repair Parts",7

12,"Logistics Support",8

13,"System Test and Evaluation",9

14,"Support Equipment",10

15,"Common Support Equipment",1

16,"Peculiar Support Equipment",2

17,"Depot Support Equipment/Rate Tooling",3

18,"Other SEIT/PM and Support Equipment",11

19,"Aero Structure (Non Stage Related)",2

20,"Stage 1-3 Interstage",3

WBS_LineItems table:

"LineItem_ID","Parent_ID","WBS_Category_ID"

1,,

2,1,

3,1,

4,3,

5,4,

6,4,

7,4,

8,4,

9,4,

10,4,

11,4,

12,4,

13,4,

14,4,

15,14,

16,14,

17,14,

18,4,

19,3,

20,3,

The temp table screen cap I gave you is built via recursive VBA and the WBS_Num column does not exist in the DB. That column is dynamically built when a form is open and is only present for the User. Below is the VBA used to build the table (if you wish to critique it please feel free, but I do understand that such feedback is outside the scope of my current question. I'm just including it for clarity). The temp_WBS table is on the user interface side and is intended to be regularly emptied and filled.

Public Sub BuildWBSHeirarchy(Optional isStaging As Boolean = False, Optional WBS_ID As Long = -1, Optional dBase As Database, Optional wbsNumber As String = "", Optional indent As Integer = 0)

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim q As String

    If (dBase Is Nothing) Then

        Set db = CurrentDb()

    Else

        Set db = dBase

    End If

    q = "SELECT WBS.LineItem_ID AS ID, LI.LineItem_Name AS Name, LI.Sibling_Pos " & _

        "FROM WBS_LineItems AS WBS " & _

        "INNER JOIN LineItems AS LI ON LI.LineItem_ID = WBS.LineItem_ID " & _

        "WHERE WBS.Parent_ID " & IIf(WBS_ID = -1, "IS NULL", "= " & WBS_ID)

    If (isStaging) Then

        q = q & " " & _

        "UNION " & _

        "SELECT SWBS.LineItem_ID AS ID, SLI.LineItem_Name AS Name, SLI.Sibling_Pos " & _

        "FROM staging_WBS_LineItems AS SWBS " & _

        "INNER JOIN LineItems AS SLI ON SLI.LineItem_ID = SWBS.LineItem_ID " & _

        "WHERE SWBS.Parent_ID " & IIf(WBS_ID = -1, "IS NULL", "= " & WBS_ID)

    End If

    q = q & _

        " ORDER BY Sibling_Pos"

    Set rs = db.OpenRecordset(q)

    Do Until rs.EOF

        '**Fix for top level items. (better way?)**

        Dim ns As String

        If (wbsNumber = "") Then

            ns = rs![Sibling_Pos]

        Else

            ns = wbsNumber & "." & rs![Sibling_Pos]

        End If

        '**End Fix**

        db.Execute ("INSERT INTO temp_WBS" _

        & "(LineItem_ID, WBS_Num, Name) " _

        & "Values (" & rs![ID] & ",'" & ns & "','" & Space(indent + 1) & rs![name] & "')")

        BuildWBSHeirarchy isStaging, rs![ID], db, ns, indent + 3

        rs.MoveNext

    Loop

    Set rs = Nothing

    Set db = Nothing

End Sub

Answer 4

I think this is what you need:

CurrentProject.Connection.Execute "ALTER TABLE WBS_LineItems ADD CONSTRAINT DistinctSiblings CHECK((SELECT LineItems.LineItem_Name FROM (LineItems INNER JOIN WBS_LineItems ON LineItems.LineItem_ID = WBS_LineItems.LineItem_ID) INNER JOIN LineItems AS LineItems_1 ON WBS_LineItems.Parent_ID = LineItems_1.LineItem_ID GROUP BY LineItems.LineItem_Name, LineItems_1.LineItem_Name HAVING COUNT(*) >1) IS NULL)"

Execute the code as a single line either in the Immediate window or in a temporary procedure in a standard module.  If you ever need to drop the constraint execute the following:

CurrentProject.Connection.Execute "ALTER TABLE WBS_LineItems DROP CONSTRAINT DistinctSiblings"

You can of course name the constraint whatever you wish rather than 'DistinctSiblings'

Answer 5

Thank you for the help. Is there some place I can see all constraints on a table or in the database?