Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: Referential integrity Vs Composite key

    Morning/afternoon/evening everyone.

    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?

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Referential integrity can be implemented, also on composite keys.
    Roy-Vidar

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    If you have problems implementing it, tell us where you're stuck.
    Roy-Vidar

  4. #4
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Hi Roy,

    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 has:

    DatabaseLocation#
    DatabaseEmployee#
    MonthCommencingDate#

    As a composite key.

    This is linked to table two which has

    DatabaseLocation# (FK)
    DatabaseEmployee# (FK)
    ContactDate
    ContactType
    AgeRange

    As composite key.

    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.

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    When you set up a relationship between two tables, you use the whole primary key, so in your case, if

    DatabaseLocation#
    DatabaseEmployee#
    MonthCommencingDate#

    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.
    Attached Thumbnails Attached Thumbnails Relationships.gif  
    Roy-Vidar

  6. #6
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    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!

  7. #7
    Join Date
    May 2012
    Posts
    7
    RoyVidar

    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:

    Table A
    Unit ID (PK) unique
    Unit Type

    Table B
    Unit ID (FK) to Table A
    Fan ID (PK)
    Motor ID (FK) to Table C

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •