Results 1 to 5 of 5
  1. #1
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,370
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Foreign Keys (2007)

    I really missing something here.

    I get that a foreign key is a field in Table_2 with the same name as a field in an accompanying table Table_1, where the field is a primary Key in Table_1, but not a primary key in Table_2.

    What I'm not seeming to get is the actual creation process.

    I have the two fields, one in each of the two tables, and using Relationships, I linked the OwnerCode field in Table_1 (PK) to the OwnerCode in Table_2 (non-PK).

    Seemed to be OK.

    What I'm not getting is:

    Now that that is set up, how does the enforced integrity work? How do I "see" it work?

    Regards,
    Chuck Billow
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Foreign Keys (2007)

    The foreign key doesn't have to have the same name as the primary key in the other table. Both fields must have the same data type though (both text, for instance, or both date/time).

    If you have ticked the "Enforce referential integrity" check box for the relationship, but not the other two check boxes in the same dialog, you won't be able to delete a record in the "main" table as long as there is at least one related record in the other table. "Related" here means that the key values are the same. You'd have to delete all related records in the other table before you can delete that record from the main table.

    If you have ticked the check box "Cascase update related fields", and if you can edit the primary key in the "main" table (i.e. it is not an AutoNumber field), all changes to the primary key field will automatically be duplicated in the foreign key field of related records in the other table. For example, if you have a record with primary key 37 in the "main" table, and you change this to 89, all foreign key values of 37 in the other table will automatically be changed to 89 too.

    If you have ticked the check box "Cascade delete related records", you *can* delete a record in the "main" table - Access will ask you whether you want to delete all related records in the other table too. You can confirm or cancel.

  3. #3
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,370
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Foreign Keys (2007)

    Hans:

    Thanks.

    Now, my (two) tables are linked, because information from Table 1 shows up (in the sub-sheets) in Table 1.

    Now, when I enter the info in Table 2, in the field that is linked to Table 1, how would I know what appropriate info might be for that field? Is there a way to have a pop-up list of available items without getting too "complicated"?

    Regards,
    Chuck
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Foreign Keys (2007)

    You can use the Lookup Wizard to create a dropdown list for you. See for example How to Use an Access 2007 Lookup List.

  5. #5
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,370
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Foreign Keys (2007)

    Hans:

    Thanks. I wasn't quite able to get the Lookup to work as yet, but it should be enough info.

    Thanks,
    Chuck
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

Posting Permissions

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