Unanswered: Referential integrity Vs Composite key
As referential integrity cannot be implemented with composite keys, I'm just interested to hear peoples comments on choosing to go with one over the other.
In the past I have stayed away from composite keys and opted for Autonumber fields as much as possible or if I have used composite keys then I have produced queries to identify duplicate rows but this has then required intervention by the user.
I would imagine that the preference would be to opt for referential integrity coupled with VBA to validate unique rows of data at the time of input?
I find myself in a situation this time where both functions are important. Any ground breaking ideas?
Unfortunately I can't upload a screenshot of the relationships, possibly due to restrictions imposed at my end. I believe my issue may be more that I need to relate two tables, both of which have composite keys.
Table one is duplicated in the Relationships window for each relationship with the foreign key and Access declares no unique index available for referential integrity. Hence the need to explore other options.
is the primary key, all those fields needs to also exist in the child table (the whole primary key).
Then, in the relationship window, select all three fields from one of the tables, drag'n'drop over to the next table. You are creating one relationship involving a composite key.
You might need to edit which fields are related inside the Edit Relationship window, but all three fields should be present for both tables. Then, if you've selected correct indexing, you should also be allowed to check the Enforce Referential Integrity button.
What you seem to have done, is trying to establish more than one relationshop.
Well I'll be an Unkeys Moncle. It's amazing how after five years of using Access that I've never sussed that! I've always chosen the 'create a new relationship' option in the past instead of 'edit existing relationship' not realising that there is scope to set up multiple joins on the existing relationship. I'll consider myself slapped! Thanks for the help Roy!
I am having similar problems with a new database I am developing. I am familiar with Paradox and was able to implement composite keys without much problem. As a matter of fact I could create a composite key with 4 fields and 2 fields were referencing 2 separate tables. I am new to Access and struggling some, but have learn a lot by trying various methods.
My tables are as follows:
Unit ID (PK) unique
Unit ID (FK) to Table A
Fan ID (PK)
Motor ID (FK) to Table C
Unit ID (FK) to Table A
Motor ID (PK)
This database is for storing Equipment, Fans, and Motors along with other types of equipment including equipment specs. My thoughts were to have a Unit Table A to store all of the different types of equipment, no duplicates. Table B will hold have a composite key Unit ID (FK), Fan ID (PK), Motor ID (FK). There are 2 other tables for equipment types that can have Fans and Motors, thus the separate table for Fans and Motors. Composite keys are setup for Fans (Table B) and Motors (Table C). Am I missing something obvious?
Also, how did you post that screenshot in your previous post?