Foreign Key to Primary Key with two columns

[mavison]

I have a problem with a table design when a Foreign Key refers to a Primary Key with two columns.

Three simple example tables (all fields text, other data fields omitted) to illustrate my problem are:

Tables: Fields
Departments: Name (PK)
Mentors: Department (PK1 and FK to Departments), Name (PK2)
Trainees: Name (PK), Department (FK to Mentors), Name (FK to Mentors)

There may be 0 or many Mentors in a Department.
Mentors.Name is only unique within a Department, so Department has to be part of PK.
Trainees may have no Department or Mentor.
Trainees may have a Department and Mentor which exists.

All the above works fine. However the problem is:

If a Trainee has a (valid) Department, but a Mentor has NOT been assigned, then Access (2003 on XP Pro) will not allow a Trainee row with a valid Department but with a blank (Null) Mentor.

I have been puzzling for some days over how to change my design to allow this last case, while retaining the Referential Integrity!

Any suggestions welcome! Have I overlooked something simple?

Thanks

 

[boblarson]

1. Don't use NAME or Department as Primary keys.

2. Use a Surrogate key (Autonumber as the primary key for your tables)

3. If you can't have duplicates of something then set a multi-field index on the table in question.

4. Name should never be used as a field or object name as it is the one Access Reserved word which can even cause problems for you if you enclose in brackets. Everything in Access (or almost everything) has a NAME property and that can come back to bite you big time.

 

[mavison]

1. Don't use NAME or Department as Primary keys.

Why?

2. Use a Surrogate key (Autonumber as the primary key for your tables)

I thought that PKs should where possible reflect an identifier of the data, not some totally artificial number?

3. If you can't have duplicates of something then set a multi-field index on the table in question.

I have got a multi-field index on Mentors - it is also the PK.

4. Name should never be used as a field or object name as it is the one Access Reserved word which can even cause problems for you if you enclose in brackets. Everything in Access (or almost everything) has a NAME property and that can come back to bite you big time.

Thanks for that warning. I will have a better look for reserved words.

None of these seem to answer how I can add a Trainee row with a valid Department and Null Mentor?

 

[boblarson]

Why?

I thought that PKs should where possible reflect an identifier of the data, not some totally artificial number?

There are a few threads here that go into it, but essentially using natural keys can be okay but more often than not some rule changes what the natural key is and that screws up your database. So, I'VE found it is best to use a surrogate key (autonumber) and just let the system do the work as it is "behind the scenes" stuff anyway and you, I, or the users need not care what it is that is keeping the relationships. Let the system do its work.

I have got a multi-field index on Mentors - it is also the PK.

No, you've got a composite key. Composite keys are a pain in the @$$ to maintain. Use an autonumber for the PK and then use the multi-field index to keep duplicate values out. Composite keys require that you store ALL of the composite fields in the other tables as well, where using a single autonumber will only store a single number. It will make your queries much simpler.

None of these seem to answer how I can add a Trainee row with a valid Department and Null Mentor?

Change your PK structure and then we can address that. In fact, I think if you change the PK structure as I've said, you will find that your problem goes away.

 

[mavison]

Ok Bob, I will try that tomorrow, and then report back. I am new to Access, so struggling a bit with the way it works best, even though I have years of experience with other RDB!

Martin

 

[georgedwilkinson]

I don't know of ANY RDBMS where it is appropriate to have a compound primary key when that key will be involved in referential integrity. The problems in Access apply equally to Oracle, MySQL, & SQL Server (and the others, back when they existed).

 

[mavison]

I seem to remember (from about 20 years ago!) that the original RDB theory was that every FK must refer to a PK, but that was modified to allow references to fields other than PK just to cater for the case I have now where a (multi-field) FK is may be (partially) null (which I had forgotten until reminded!). If the FK was never null, then it should probably refer to a PK. But my memory is a little hazy ...

In 15 years working with DB2 and very large commercial databases I could count on one hand the number of artificial PKs used - and RI was used extensively!

Anyway, now off to experiment with Bob's suggestions ...

 

[namliam]

Just another voice to add some support

Using natural keys is basicaly not a problem in itself, using a name for anything is... How many "Mr. Smith" are there? Or how many departments "administration" ??
Names by their nature are not unique even if the SEEM to be at the moment.

Using an Autonumber will ensure you have a (invisible) database Primary key, one that will be unaffected by "user definition" and "business logic" how ever perminant a department name or customer name may seem, there are always exceptions and things like this WILL change causing your database to have a major headache cause the PK needs to change with all RI issue as a result.

A composit key CAN be a great asset, but rarely turns out to be one... In particular if said composit key is then a FK in many tables replicating all those columns Headache galore! Substituting your composit key by a (hidden) autonumber primary key usually makes life a lot easier down the road

As for your trainee problem,
Trainees: Name (PK), Department (FK to Mentors), Name (FK to Mentors)
Your trainess have basicaly 1 foreign key in this design which is the combination of Dept + Mentor Name.
Given Mentor name is part of a primary key in that table and the fact that Null values cannot exist in any PK... then this is your problem... A design one where your PK is basicaly causing you problems.

 

[mavison]

In the light of comments, I have revised my design to see how it would work with each table with an artificial key:

Tables: Fields
Departments: DeptId (PK) , DeptName
Mentors: MentId (PK) , MentName , DeptId (FK to Departments.DeptId)
Trainees: TrainId (PK), TrainName, DeptId (FK to Departments.DeptId), MentId (FK to Mentors.MentId)

There would be unique indexes on
Departments.DeptName
Mentors.DeptId + MentName

There may be 0 or many Mentors in a Department.
Trainees may have no Department or Mentor, so Trainees.DeptId and MentId can be Null.
Trainees may have a Department which exists, but no Mentorso MentId can be Null.
Trainees may have a Department and Mentor which exists.

All the above would work fine, except that because the FK are now to Id fields, the problems now seem to be that the RI cannot now :

1. enforce that a Trainee row has a Mentor which must be from the same Department that is in the Trainee row.
2. stop changes to Departments.DeptName, or Mentors.MentName, or Mentors.DeptId that would make Trainees data incorrect.

I do realise that code could probably be added in Forms to check these, but that seems the wrong place to do it!

Note that these tables are only the mimimal examples just to illustrate my quandary! It is also true that the numbers of rows in the actual tables is small - less than 100.

 

[namliam]

Well you cannot have your cake and eat it too...
One option excludes the other...