microsoft access append query key violation when there is no primary key

Question

microsoft access append query key violation when there is no primary key

Harold E1 25

I have Access as a subscription of Microsoft 365 under Windows 11 Pro.

I am trying to run an append query, but I get an error message: "didn't add records due to key violations".

However, the table I am trying to append it to, doesn't have ANY indexes, including a primary key!

How can there be a key violation, when there is no key?!?

0 comments

1 answer

Your answer

Answer 1

Teddie Dang 1,365 Independent Advisor

Hi @Harold E1

In Microsoft Access, a key violation does not necessarily mean that the destination table has a primary key. It can also occur because of a unique index.

According to Microsoft’s documentation, an append query can report key violations when the data conflicts with a primary key or an index in the destination table that does not allow duplicate values.

You should therefore check the destination table in Design View > Indexes and make sure there isn't a unique index that you may have overlooked. This is also consistent with a Microsoft Q&A case where an unexpected key violation turned out to be caused by a unique index that was not initially noticed.

Also, check the table in Database Tools > Relationships to see whether it participates in any relationships with Enforce Referential Integrity enabled. An enforced relationship can prevent records from being appended if the values being inserted do not satisfy the relationship.

Teddie Dang 1,365 Reputation points Independent Advisor

2026-09-29T02:47:07.38+00:00

Hi @Harold E1

Just checking in to see if the information above was useful to you. If you have any additional updates or need further assistance, don't hesitate to reach out. Warm thanks!
Harold E1 25 Reputation points

2026-09-30T02:04:56.7+00:00

not only was there no primary key, but there were also no indexes of ANY kind.

And what is really strange is that some records were appended, but not others.

I finally gave up and solved my problem another way. I exported the existing table to Excel. I then pasted into Excel the records I was trying to append, and then I uploaded ALL the data into a completely new table and deleted the old one.
Teddie Dang 1,365 Reputation points Independent Advisor

2026-09-30T02:22:37.8366667+00:00

Hi @Harold E1

Thank you for the update and for clarifying that there were no indexes of any kind on the destination table. With no primary key or unique indexes, the usual explanation for a key violation does not appear to fit your case. It suggests that there may have been something specific to the records that failed or another constraint involved in the append operation.

In any case, I'm glad you were able to work around the issue by rebuilding the table and successfully import all of the data. Thank you as well for sharing the solution you used, as it may be helpful to others who encounter similar behavior.