Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Referential Integrity (Access 2003 SP2)

    Do i check the Referential Integrity for the joins to a lookup table? I would think i wouldn't do this.

    Is the only time that referential integrity is used to ensure that a master record is present before a slave record is added?

    In the case of a Order Header and Order Lines, do we would use referential integrity? I would assume yes.

    In the case of where a lookup table must be there, do we use referential integrity? I would assume yes.

    In the case of where a lookup table entry needn't be there, do we use referential integrity? I would assume no.

    Advice please, i have got myself confused.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Referential Integrity (Access 2003 SP2)

    I agee with most of your assertions, but as well it is only if you enforce referential integrity that you can do cascading updates and deletes.

    So enforcing integrity (with cascading deletes) between and Order Header and Order details is useful. Delete the Header and the Details are automatically deleted.
    ( I would use that in a Cancel button on a New Order Form)

    If you have Lookup table with just a textfield (rather than a number key field and a second text field) then Cascading Updates are useful.
    If you reword the text in the textfield, then the uses of it are updated.
    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Referential Integrity (Access 2003 SP2)

    Thanks John, i had amended all my joins correctly before you answered, but thank you anyway.

    I don't have lookup tables with text keys, i always use a autonumber/number as the keys.

    I usually steer clear of cascading deletes and update.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Referential Integrity (Access 2003 SP2)

    If you use an autonumber key for the lookup table then integrity still matters. For two reasons:

    Without it the Main table can hold meaningless numbers, and secondly if you create queries that join the Main table to the lookup, you need to be fussy about the join type or records can be left out of the results.
    Regards
    John



  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Referential Integrity (Access 2003 SP2)

    The only problem with setting referential integrity in that case is that there must be a lookup table entry for the main table.

    This was a problem i was having initially. ie. i had set referential integrity between 2 tables and i could not write the main table record. As soon as i took off referential integrity it allowed me to write a main table entry with no associated lookup table entry.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Referential Integrity (Access 2003 SP2)

    Does your main table have default values of 0 for the lookup fields? They violate the integrity as the lookup table wll not have a 0 entry.
    Regards
    John



  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Referential Integrity (Access 2003 SP2)

    Of course, i had forgotten that. Access automatically assumes you want a default of 0 for number type fields.

    So referential integrity is checked for non null fields.

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

    Re: Referential Integrity (Access 2003 SP2)

    With AutoNumber keys, you don't need cascading updates.

    Cascading deletes can be useful, but it depends on the situation. Here is an example where it came in very handy:

    Users wanted to be able to assign user-definable categories to clients, in order to select clients by category. Since a client can belong to several categories, it is a many-to-many relationship, so there is a client-category table.
    Some categories are permanent, but others are used for a quick one-off purpose. When such a transient category is deleted, it is very convenient to have all related records in the client-category table deleted automatically.

    Of course, there are other situations in which you want to prevent the user from deleting a record as long as there are related record in other tables.

Posting Permissions

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