none
Key Violations RRS feed

  • Question

  • I have two tables that are identical (one is a structure copy of the other). One is Members the other is Past Members.  I want to append PastMembers with Members that are no long members.  The criteria in the Status field would = 4 (meaning no longer members) When I run the append query, I get a key violation error for all but one record. Any ideas how to rectify this?

    "A computer lets you make more mistakes faster than any invention in human history -- with the possible exceptions of hand guns and tequila." Mitch Ratliffe

    Wednesday, November 25, 2015 2:18 PM

Answers

  • >>>Actually.  I did something different.  I ran a backup of the database, opened the back up, ran it, and my query worked fine.  Must've been a glitch in the db!

    When you restore an entire database, you replace a database file that is damaged, has data problems, or is missing altogether, with a backup of the entire database.

    So I suggest that you could make sure that there are no below issues when you get Key violations error:

    1.The primary key must have a unique value. If you try to import a record where the primary key value is 9, and you already have a record where the primary key is 9, the import fails due to a violation of the primary key.

    2.You can also violate a foreign key. For example, if you have a field that indicates which category a record belongs to, you will have created a table of categories, and established a relationship so only valid categories are allowed in this field. If the record you are importing has an invalid category, you have a violation of the foreign key.

    3.You may have other unique indexes in your table as well. For example, an enrolment table might have a StudentID field (who is enrolled) and a ClassID field (what class they enrolled in), and you might create a unique index on the combination of StudentID + ClassID so you cannot have the same student enrolled twice in the one class. Now if the data you are importing has an existing combination of Student and Class, the import will fail with a violation of this unique index.

    • Marked as answer by David_JunFeng Monday, December 7, 2015 3:25 PM
    Thursday, November 26, 2015 8:44 AM

All replies

  • Sounds like you have duplicate data.   Take a look at the tables in design view and for each Field see if any are "Indexed" with Yes (No Duplicates) as a property. 

    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Wednesday, November 25, 2015 2:54 PM
  • I have two tables that are identical (one is a structure copy of the other). One is Members the other is Past Members.  I want to append PastMembers with Members that are no long members.  The criteria in the Status field would = 4 (meaning no longer members) When I run the append query, I get a key violation error for all but one record. Any ideas how to rectify this?

    Hi Library TechChik,

    You could make the unique keys values for the two tables different. What is the unique key?

    Imb.


    • Edited by Imb-hb Wednesday, November 25, 2015 3:29 PM typo
    Wednesday, November 25, 2015 3:28 PM
  • Actually.  I did something different.  I ran a backup of the database, opened the back up, ran it, and my query worked fine.  Must've been a glitch in the db!


    "A computer lets you make more mistakes faster than any invention in human history -- with the possible exceptions of hand guns and tequila." Mitch Ratliffe

    Wednesday, November 25, 2015 3:59 PM
  • >>>Actually.  I did something different.  I ran a backup of the database, opened the back up, ran it, and my query worked fine.  Must've been a glitch in the db!

    When you restore an entire database, you replace a database file that is damaged, has data problems, or is missing altogether, with a backup of the entire database.

    So I suggest that you could make sure that there are no below issues when you get Key violations error:

    1.The primary key must have a unique value. If you try to import a record where the primary key value is 9, and you already have a record where the primary key is 9, the import fails due to a violation of the primary key.

    2.You can also violate a foreign key. For example, if you have a field that indicates which category a record belongs to, you will have created a table of categories, and established a relationship so only valid categories are allowed in this field. If the record you are importing has an invalid category, you have a violation of the foreign key.

    3.You may have other unique indexes in your table as well. For example, an enrolment table might have a StudentID field (who is enrolled) and a ClassID field (what class they enrolled in), and you might create a unique index on the combination of StudentID + ClassID so you cannot have the same student enrolled twice in the one class. Now if the data you are importing has an existing combination of Student and Class, the import will fail with a violation of this unique index.

    • Marked as answer by David_JunFeng Monday, December 7, 2015 3:25 PM
    Thursday, November 26, 2015 8:44 AM