Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question about Enforcing Referential.. (2000)

    I have a table listing staff and one listing the offices they work at. I've linked them using the lookup wizard so that the office at which a staff member works can be selected from a drop-down. I then enforced referential integrity.
    Because I'd already keyed a couple of records, one of the staff did not have an office selected. I thought this wasn't supposed to be allowed when referential integrity is enforced?! If I select an office for this person and try and delete the office it keeps reappearing, or if I put a space in this field and press Enter the space is replaced with a Null value. Why? What's happening? Andy.

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

    Re: Question about Enforcing Referential.. (2000)

    Enforcing referential integrity does not imply that every record on the "many" side MUST have a non-null value in the link field. It only means that if there is a non-null value, it must be valid, i.e. it must be from the table on the "one" side.

    I don't understand why you are not able to delete an office, unless you have also set the office field to be required.

    Access always trims trailing spaces from text entered into a table, query or form, so if you enter just a space in a field, that space is deleted when you tab out of the field.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Question about Enforcing Referential.. (2000)

    If you enforced referential integrity, how can you have a person without an office? Logically, if you selected an office and referential integrity requires that one be there, you can't delete the value unless you replace it with an equally valid value. I would suggest that you add a rplaceholder ecord to your office table, something like "undetermined" or "none selected" and use that for records where the office is unknown.
    Charlotte

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Question about Enforcing Referential.. (2000)

    What happens when you hit the spacebar when in a field (that is, whether/not it is converted to null) depends on a couple of settings for the field: whether/not it is required and whether/not zero-length strings are allowed. See Help for an explanation.

    As Hans mentioned, you can have an "orphan" record on the many side if the connecting field is Null. A way to avoid this is to make that field required.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Question about Enforcing Referential.. (2000)

    It seems that Access is behaving 'normally' with the behaviour I have described, although I think it odd that if I delete the selected office for a staff member it reappears straight-away and I have to enter a space to allow Access to then convert this to the null value that I might want!

    I generally try to avoid changing the Required option to Yes, but it seems that I will now have to consider this every time I enforce r.i. Thanks all for the help. Andy.

Posting Permissions

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