Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Error: Data in table violates referential integrity rules.

Discussion in 'Information Technology' started by rg, Jul 28, 2006.

  1. rg

    rg
    Expand Collapse
    Guest

    I am trying to enforce referential integrity for a relationship and getting
    this error. As far as I can tell, the records in the child table all have a
    corresponding record in the parent table, so I don't know what the problem
    is. I created a form which listed the fields from the child table and the
    parent table, then looked at each record. The information from both tables
    was included in each record. I don't know what else could be giving me this
    error. Any ideas?
    -rg
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    A common cause of this error is a foreign key field that has a default value
    assigned.

    For example, Access gives a Number field the Default Value of zero. If you
    don't assign a value to this field, it automatically gets the zero value,
    which fails to match any value in the main table, even though you did not
    specifically assign a value for this field.

    Don't give up on the Referential Integrity. It is worth tracing this down.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "rg" <rg@discussions.microsoft.com> wrote in message
    news:0584BC34-42F2-400D-AF55-F8CE9FB80EA7@microsoft.com...
    >I am trying to enforce referential integrity for a relationship and getting
    > this error. As far as I can tell, the records in the child table all have
    > a
    > corresponding record in the parent table, so I don't know what the problem
    > is. I created a form which listed the fields from the child table and the
    > parent table, then looked at each record. The information from both
    > tables
    > was included in each record. I don't know what else could be giving me
    > this
    > error. Any ideas?
    > -rg
     
  4. rg

    rg
    Expand Collapse
    Guest

    Thanks for your reply,

    All the fields that are used for linking are filled in with information.
    The two fields (one in parent, one in child), are text fields. When I scroll
    through the records within the form I created that includes all the fields
    from both tables, each record has the information listed from both tables.
    Do you know what else could be wrong?
    -rg

    "Allen Browne" wrote:

    > A common cause of this error is a foreign key field that has a default value
    > assigned.
    >
    > For example, Access gives a Number field the Default Value of zero. If you
    > don't assign a value to this field, it automatically gets the zero value,
    > which fails to match any value in the main table, even though you did not
    > specifically assign a value for this field.
    >
    > Don't give up on the Referential Integrity. It is worth tracing this down.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    If the fields are all accounted for, and all have values, then the values
    must be mismatched.

    Examples: one field is shorter than the other (fewer characters), or the
    entries are not identical (e.g. leading space), or one has a zero-length
    space where the other has a null, or ...

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "rg" <rg@discussions.microsoft.com> wrote in message
    news:05D84543-4200-4848-8DC4-3E6BC5DFB091@microsoft.com...
    > Thanks for your reply,
    >
    > All the fields that are used for linking are filled in with information.
    > The two fields (one in parent, one in child), are text fields. When I
    > scroll
    > through the records within the form I created that includes all the fields
    > from both tables, each record has the information listed from both tables.
    > Do you know what else could be wrong?
    > -rg
    >
    > "Allen Browne" wrote:
    >
    >> A common cause of this error is a foreign key field that has a default
    >> value
    >> assigned.
    >>
    >> For example, Access gives a Number field the Default Value of zero. If
    >> you
    >> don't assign a value to this field, it automatically gets the zero value,
    >> which fails to match any value in the main table, even though you did not
    >> specifically assign a value for this field.
    >>
    >> Don't give up on the Referential Integrity. It is worth tracing this
    >> down.
     
  6. rg

    rg
    Expand Collapse
    Guest

    Thanks for your reply. I makes sense what you say unfortunately, I can't see
    any way to find out. If the values are mismatched, would the child record
    pull in the data from the correct parent record in the form I've created. If
    so, how am I going to find the records that don't match. I created the form
    so that I could find out what, if any parent record connected with my child
    records. All records are accounted for and matched appropriately based on
    the info in my form, so I'm at a total loss for finding out what values are
    mismatched.

    "Allen Browne" wrote:

    > If the fields are all accounted for, and all have values, then the values
    > must be mismatched.
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    2 suggestions: divide and conquor, and enlist Access to help.

    If there are several indexes on the table, remove some. Remove the relations
    too if needed. Once it starts working, you have identified which one is the
    problem.

    Then restore that index/relation, and when it fails, use the Immediate
    Window (Ctrl+G) to ask Access what the values are in the mismatched fields.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "rg" <rg@discussions.microsoft.com> wrote in message
    news:C3ED60E3-A904-4196-AFF3-BAA5F1CD0D5E@microsoft.com...
    > Thanks for your reply. I makes sense what you say unfortunately, I can't
    > see
    > any way to find out. If the values are mismatched, would the child record
    > pull in the data from the correct parent record in the form I've created.
    > If
    > so, how am I going to find the records that don't match. I created the
    > form
    > so that I could find out what, if any parent record connected with my
    > child
    > records. All records are accounted for and matched appropriately based on
    > the info in my form, so I'm at a total loss for finding out what values
    > are
    > mismatched.
    >
    > "Allen Browne" wrote:
    >
    >> If the fields are all accounted for, and all have values, then the values
    >> must be mismatched.

    >
     

Share This Page