Append query causes key violation

Question

Greetings,

I know this question has been answered several times, but every article/post I search on does not fully address my situation, so I will attempt to be as specific as possible where other posts have failed in this regard:

1. I am creating a simple append query where I desire to append records from Table B to Table A.
2. I am attempting to append all records from Table B into Table A.
3. Table B has the exact same structure (name, number, and type of fields) as Table A.
4. I only desire to append new records to Table A with *certain* (not all) fields from Table B (appending a subset of fields of B/A).
5. I am not attempting to append any PK from Table into the corresponding PK in Table A.  (I just expect Table A to autogenerate new record ids as it normally does when I bolt on records to it.)
6. Both Table A and Table B use a single autonumber field called ID as the key.  If there is some other key or index situation going on, I am unaware of it.
7. There is only one field that I am attempting to add for each record from Table B which contains a numeric value which, when it goes into Table A, is related to values in a foreign (reference table).  The field is a single number id representing a document id; the documents and their ids are in Table C.
8. Every record in Table B has other 'keyed' fields to other reference tables, but none of those fields are being copied over from Table B because I am not taking the time now to fill those values in (too much work at the moment).  I only bring this up because, for example, if I try to manually add a new record to Table A, it defaults those fields to the value of 0, and 0 is not generally a valid value in those reference tables (because the indexes always start autonumbering from 1).

I am wondering if for item 7. I should clear it out and use an update query to set it later on.  My reasoning is that I took a table that had such a field in it, in another working append query, but the field was blank; after the records were successfully appended, I did an update query to set the value in Table B.  Me doing it ahead of time was just for the sake of knowing what the value would be for all records of Table B.  (Alternately, I could adjust my query under development to not copy that numeric field over, since that's the only one that's implicitly linked to a table in any fashion -- Table C.)

Is what I'm attempting legal?  What might possibly be causing my error?

Mike

Answer 1

Hello Mike

Any numeric field which is a key (either primary or foreign) in a relationship should never have a DefaultValue.  Personally I think it is bad to have new numeric fields default to a DefaultValue of 0, and I and others have been campaigning to have this changed for 20 years to no avail :-(

So, first remove the DefaultValue from all the foreign key fields in Table A.

Next, check that none of the fields which are not populated by your append query have the Required property set.

All the best!

Graham

Answer 2

For what it's worth, here's the query (Table A = tblRequirements, Table B = tblSRS_Requirements_Imported_ECP78_ONLY_Edited):

INSERT INTO tblRequirements ( [Requirement ID], [Parent Requirement ID], [Source Document ID], [Source Document Reference], [Requirement Text], ECP, Comments, [AVT Comments], [Verification Method] )

SELECT tblSRS_Requirements_Imported_ECP78_ONLY_Edited.[Object Identifier], tblSRS_Requirements_Imported_ECP78_ONLY_Edited.[Source Identifier(s)], tblSRS_Requirements_Imported_ECP78_ONLY_Edited.[Source Document ID], tblSRS_Requirements_Imported_ECP78_ONLY_Edited.[Source Document Reference], tblSRS_Requirements_Imported_ECP78_ONLY_Edited.[Requirement Text], tblSRS_Requirements_Imported_ECP78_ONLY_Edited.ECP, tblSRS_Requirements_Imported_ECP78_ONLY_Edited.Comments, tblSRS_Requirements_Imported_ECP78_ONLY_Edited.[AVT Comments], tblSRS_Requirements_Imported_ECP78_ONLY_Edited.[Verification Method]

FROM tblSRS_Requirements_Imported_ECP78_ONLY_Edited;

Answer 3

Hello Graham,

I will do the following:

• Reset my numeric fields (those linked to foreign tables) to not have a default value (which is a pain even outside of me trying this append query) so that it doesn't put zeroes in as defaults (I left them as zeroes so that I knew which values I would have to change later, but I've seen that I can't advance past a given record until ALL such fields have a valid value based on the foreign table).
• Remove the current number from the column of data I'm trying to append (I can always create an update query later to reset all the new, appended records to have that column have the proper value, which is 4 in my case)

and let everyone know what happens.  I might not get to it today, perhaps next week, as things are quite busy around here at work.

Mike

Answer 4

Neither is.  Both fields are Short Text in both tables.

Mike

Answer 5

And this query is failing because of key violations?  Well first, Is either Requirement ID or Parent Requirement ID a PK?