Databases
StackOverflow2013
Postsdbo
POIs it possible to use a field from a referenced (via foreign key) grandparent row in a child table's composite foreign key?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs it possible to use a field from a referenced (via foreign key) grandparent row in a child table's composite foreign key?
    primarykey
    data
    text
    <h1>Full Question:</h1> <p>From a child table linked to a row in its grandparent table by a foreign key (references grandparent primary key), is it possible to use a field from the referenced grandparent row in the child table's composite foreign key?</p> <p>My current database design is has a main parent table, <strong>Projects</strong>. <strong>Projects</strong> has two child tables, <strong>JobTitles</strong> and <strong>Tasks</strong>. <strong>Tasks</strong> then has a child table <strong>Subtasks</strong>. <strong>Subtasks</strong> then has a child table <strong>Assignments</strong>, which is a x-ref table putting employees on <strong>Subtasks</strong>. The problem I've come up to is assigning <strong>JobTitles</strong> to <strong>Assignments</strong>; <strong>JobTitles</strong> belong to a <strong>Project</strong>, and as such the <strong>Assignments</strong> under any given <strong>Project</strong> should only be able to reference <strong>JobTitles</strong> that share a <strong>Project</strong>. I recently asked <a href="https://stackoverflow.com/questions/11228453/how-to-represent-a-categorized-many-to-many-database-relationship-where-categori">this question</a> regarding limiting choice of <strong>JobTitle</strong> to those sharing a project. However, since then I've found a composite foreign key to be a much cleaner solution.</p> <h1>Simple database layout:</h1> <ul> <li>Projects <ul> <li>JobTitles</li> <li>Tasks <ul> <li>Subtasks <ul> <li>Assignments</li> </ul></li> </ul></li> </ul></li> </ul> <p>I've found how to create a composite key <a href="https://stackoverflow.com/questions/9780163/composite-key-as-foreign-key-sql">here</a>, but for this I would need to use the Project's primary key as part of the composite foreign key.</p> <h1>Tables:</h1> <ul> <li>Projects <ul> <li>ProjectName <strong>(PK)</strong></li> <li>ProjectID (Unique Index)</li> </ul></li> <li>JobTitles <ul> <li>JobTitle</li> <li>ProjectID (Foreign Key -> Projects.ProjectID) <strong>(Composite PK: JobTitle-ProjectID)</strong></li> <li>JobTitleID (Unique Index)</li> </ul></li> <li>Tasks <ul> <li>TaskName</li> <li>ProjectID (Foreign Key -> Projects.ProjectID) <strong>(Composite PK: TaskName-ProjectID)</strong></li> <li>TaskID (Unique Index)</li> </ul></li> <li>Subtasks <ul> <li>SubtaskName</li> <li>TaskID(Foreign Key -> Tasks.TaskID) <strong>(Composite PK: SubtaskName-TaskID)</strong></li> <li>SubtaskID (Unique Index)</li> </ul></li> <li>Assignments <ul> <li>EmployeeID (Foreign Key)</li> <li>SubTaskID (Foreign Key -> Subtasks.SubtaskID) <strong>(Composite PK: EmployeeID-SubtaskID)</strong></li> <li>JobTitleID (Foreign Key -> JobTitles.JobTitleID)</li> <li>AssignmentID (Unique Index)</li> </ul></li> </ul> <p>In order to assign a JobTitle to an Assignment, I want to set up a composite foreign key using an Assignment's ProjectID (from its parent Task) and its selected JobTitleID. The only problem is that I have no idea how to grab the ProjectID, which is two generations away, for use in a key. It could be possible to pass the ProjectID down through each generation by wrapping it in a composite key within each table, but composite keys aren't something something to throw around willy nilly considering the toll they take on performance (not to mention passing the value down seems a bit sloppy). Is there any way to get to the ProjectID for use in the key without passing it through the other tables?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USPeaBucket
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload