Foreign Key Error thrown

Question

Post reply

Foreign Key Error thrown

doc_sewell

SSC Eights!

Points: 820
More actions
April 20, 2011 at 1:30 pm

#235027

I have a SQL job (T-SQL) that inserts data from a proxy table to an audit table that foreign keys to a transactional table. The job runs fine processing the proxy table records but then throws the error: "The INSERT statement conflicted with the FOREIGN KEY constraint... "

This job ran fine but the audit table is now 336 million rows and 32GB in size. The indexing is 21GB with 4GB just for the FK index. So, what do I do? Drop and recreate the FK and/or FK index?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd have to see details before I could recommend anything. Table structures, et al, will be needed in order to recommend anything sensible.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

Certainly there has been something wrong with a foreign key value for the inserted batch.

I would query the source table using a where not exists correlated query using the foreign key constraint primary key table.



select * /* (yes * is ok for this purpose) */

from mysource S

where not exists ( select * from FK_ParentTable PK where PK.primarykeycol(s) = S.FK_COL(s)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

doc_sewell SSC Eights! Points: 820 More actions · Answer 3

No, it returns zero. The FK appears to be working. Remember the process is like this:

ParentTable ChildTable ProxyTable

PKCol (int) FKCol (int) Receives New Records to Process

Transactions occur and changes to existing accounts become new records in the ProxyTable. A SQL job runs every minute and processes those records, which includes an INSERT into the ChildTable. This INSERT works fine, but the job returns an INSERT error about violating the Foreign key between the Child and Parent.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 4

It doesn't accept invalid FK values.

Please post ddl (the actual please) of your involved tables as well as the sqljob giving the error as well as the actual error message.

(you can script it all including the system constraint names)