Parent child primary key issue in multi-level relationship

[SpecLED1]

Hi - Just joined. Developing our first Access application. We using the O'Reilly Access 2013 'The Missing Manual' as our guide. Here's what's up:

We have no key constraint issues for a child of a top level parent table. But when the child table has its own parent child relationship we do have key constraint issues. If the second level table has 2 key field and two rows,
the first row key1 value 'A' and key2 value 'B' and the second row with key1 value 'B' and key2 value 'A', the third level child of this table with 3 key fields is allowing key1 value 'A' and key2 value 'A'. How do we establish the Primary Key Index field COMBINATION of the parent as the one-to-many relationship. The O'Reilly manual has us establishing one-to-may relationships with each key field of the parent table. In this example, we cannot enter values 'A' and 'C' in the lowest level table because 'C' is not found in key2 on the parent table. But we can still use an invalid combination of individually valid values.

THANKS

 

[MarkK]

I would just use a single autonumber field as the primary key in any table. If you also require that certain combinations of data in other fields be unique, you can do that with an index.

If you enforce referential integrity on the parent/child relationship between table, then you will not be able to add a child record unless the foreign key in that record links to a valid record in the parent. If referential integrity is not enforced, then you should be able to freely add records to the child table without a valid link to a parent.

But creating a primary key with data from a combination of fields seems like complexity without advantage to me.

 

[SpecLED1]

MarKK - I REALLY appreciate the quick response.

Forgive me if this reply is wordy, but I think a data example is easier for me to express than the technology which I quite obviously don't completely understand ...

If I have 3 tables: a table for Schools with a key field School (autonum if you like), and a subordinate child table for Students keyed by School and StudentID, and a table for ClassEnrollment keyed by School, StudentID and ClassName and I have the following data ...

School table with 2 rows having keys 1, 2
Student table with 2 rows having keys (1 + Arthur) and (2 + Jeff)

... then I don't expect to be able to enter a ClassEnrollment row with keys (1 + Jeff + Algebra). 1 is a valid value for School, Jeff is a valid value for student, but the combination is not valid as Jeff is in school 2 on the Student table. I guess I have 2 questions
1. How do I make the parent child relationship work from the Student table to the ClassEnrollment table with multiple key fields?
2. If that isn't what you'd do, how would you key these tables to avoid this complexity?

Thanks for your patience...

 

[MarkK]

I'm not certain that I have your design correct, but off the top of my head I would do...

tSchool
SchoolID (Autonumber Primary Key)
Name

tStudent
StudentID (Auto. PK)
Name

tEnroll
EnrollID (Auto. PK)
SchoolID (Long Int. FK)
StudentID (Long Int. FK)
EnrollDate

So that's how I'd do the keys, not guaranteeing my design is to your purpose, but this...

1 + Jeff + Algebra

...doesn't make sense as a key, to me. Use long integers, and the child row will link to the autonumber unique ID in the parent table. The child table, of course, might have many rows with the single parent's ID -> One-to-Many.

Hope this helps,