By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Append Query & Key Violations Error

P: 35
Hi

I have a problem appending data into an oracle table from access. I imported the new data from an excel sheet into a table in access and useed an append query to add the data into a linked oracle table. The linked table has a composit primary key (Code, Org). The table in access has new data that are not in the oracle table and the data in the PK fields are not duplicated. I also made sure that both tables have same data types.

I created an append query that adds the records from the imported table into the linked one. The append query is done successfully on a copy of the linked table, however I keep getting the Key Violation error when I try it on the linked table. It says that access didn't add 932 (All records) record(s) to the table due to key violations. The table has lots of fields but I want to add only the fields in the query below.

Here is my query:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO D7ITEST_R5OBJECTS ( OBJ_OBTYPE, OBJ_OBRTYPE, OBJ_CODE, OBJ_DESC, OBJ_RSTATUS, OBJ_RECORD, OBJ_GROUP, OBJ_USER, OBJ_ORG, OBJ_LOCATION, OBJ_MRC, OBJ_COMMISS, OBJ_STATUS, OBJ_DEPEND, OBJ_PRODUCTION, OBJ_NOTUSED, OBJ_SAFETY, OBJ_STATE, OBJ_RSTATE )
  2. SELECT R5OBJECTS.[OBJ OBTYPE], R5OBJECTS.[OBJ OBRTYPE], R5OBJECTS.[OBJ CODE], R5OBJECTS.[OBJ DESC], R5OBJECTS.[OBJ RSTATUS], R5OBJECTS.[OBJ RECORD], R5OBJECTS.[OBJ GROUP], R5OBJECTS.[OBJ USER], R5OBJECTS.[OBJ ORG], R5OBJECTS.[OBJ LOCATION], R5OBJECTS.[OBJ MRC], R5OBJECTS.[OBJ COMMISS], R5OBJECTS.[OBJ STATUS], R5OBJECTS.[OBJ DEPEND], R5OBJECTS.[OBJ PRODUCTION], R5OBJECTS.[OBJ NOTUSED], R5OBJECTS.[OBJ SAFETY], R5OBJECTS.[OBJ STATE], R5OBJECTS.[OBJ RSTATE]
  3. FROM R5OBJECTS LEFT JOIN D7ITEST_R5OBJECTS ON (R5OBJECTS.[OBJ CODE] = D7ITEST_R5OBJECTS.OBJ_CODE) AND (R5OBJECTS.[OBJ ORG] = D7ITEST_R5OBJECTS.OBJ_ORG);
Nov 10 '08 #1
Share this Question
Share on Google+
10 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, MeeMee.

Your query is expected to return all records from [R5OBJECTS] including those already present in [D7ITEST_R5OBJECTS].
You've missed WHERE clause
Expand|Select|Wrap|Line Numbers
  1. ... WHERE (D7ITEST_R5OBJECTS.OBJ_CODE IS NULL) AND (D7ITEST_R5OBJECTS.OBJ_ORG IS NULL)
  2.  
Regards,
Fish.
Nov 10 '08 #2

P: 35
Hello, MeeMee.

Your query is expected to return all records from [R5OBJECTS] including those already present in [D7ITEST_R5OBJECTS].
You've missed WHERE clause
Expand|Select|Wrap|Line Numbers
  1. ... WHERE (D7ITEST_R5OBJECTS.OBJ_CODE IS NULL) AND (D7ITEST_R5OBJECTS.OBJ_ORG IS NULL)
  2.  
Regards,
Fish.
I am still getting the error after adding the where clause, the records I am adding into the linked table are not there in the linked table so it is strange i am getting key violations since the combination of the PK is not there in the linked table
Nov 10 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Do you have any other constraint (but primary key) in the table?
Or maybe triggers?
Nov 10 '08 #4

P: 35
Do you have any other constraint (but primary key) in the table?
Or maybe triggers?
there are some NOT NULL fields but I made sure they have data, some default values on some columns and a unique constraint but they allow null, and some foreign keys, it is a huge table but i am adding data to some fields
Nov 11 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Everything is ok but doesn't work either ... ;) Very common situation.
What could I suggest ...

Being on your place I would first try to fimd circumstances when record could be added to the target table - even remove one existing record and append it back to the table. As soon as a record is being appended succesfully I would analyze difference between this "good" record and a record you try to append from [R5OBJECTS] and make various sequential changes to that "good" record towards "bad" record to localize error source.

Regards,
Fish

P.S. Did you check whether the table have any triggers (particularly append trigger)?
Nov 11 '08 #6

P: 35
Everything is ok but doesn't work either ... ;) Very common situation.
What could I suggest ...

Being on your place I would first try to fimd circumstances when record could be added to the target table - even remove one existing record and append it back to the table. As soon as a record is being appended succesfully I would analyze difference between this "good" record and a record you try to append from [R5OBJECTS] and make various sequential changes to that "good" record towards "bad" record to localize error source.

Regards,
Fish

P.S. Did you check whether the table have any triggers (particularly append trigger)?
Yes , I checked for triggers, it doesn't have any .. I tried adding a new record , only one, again got the same error, I'll try the method you mentioned ..

Thanks alot :)
Nov 11 '08 #7

P: 35
one question, the tables has some foreign keys refrencing other tables, I am not using these fields in the append query, I don't want to assign values to them, could this be causing the problem ??
Nov 11 '08 #8

FishVal
Expert 2.5K+
P: 2,653
one question, the tables has some foreign keys refrencing other tables, I am not using these fields in the append query, I don't want to assign values to them, could this be causing the problem ??
Ok.

I would repeat it once more.
Being on your place I would focus my efforts on localizing error source.
For me it means determining borders of conditions where records addition works, no matter how far they are from what you want to achieve.

Maybe it is better to test first whether you could add record to ANY table - better with some simple table specially created for this. If it works then the problem is specific for your particular table, connection and general database security are not guilty.

Then I would try to add to the target table a record as "good" as possible - all fields have values, PK fields (and other unique indexes) contain unique values, FK fields contain values existing in correspondent tables etc.

And so on in a similar manner.

Regards,
Fish
Nov 11 '08 #9

P: 35
Ok.

I would repeat it once more.
Being on your place I would focus my efforts on localizing error source.
For me it means determining borders of conditions where records addition works, no matter how far they are from what you want to achieve.

Maybe it is better to test first whether you could add record to ANY table - better with some simple table specially created for this. If it works then the problem is specific for your particular table, connection and general database security are not guilty.

Then I would try to add to the target table a record as "good" as possible - all fields have values, PK fields (and other unique indexes) contain unique values, FK fields contain values existing in correspondent tables etc.

And so on in a similar manner.

Regards,
Fish
I managed to add one record by filling in the empty fields and testing, I found that there was one field that needed to be given a value although it is not a mandotry field and accepts Null .. Problem solved :)

thanks alot
Nov 11 '08 #10

FishVal
Expert 2.5K+
P: 2,653
You are welcome, MeeMee.

Best regards,
Fish
Nov 11 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.