April 20, 2011 at 1:30 pm
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?
April 20, 2011 at 1:33 pm
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
April 20, 2011 at 1:54 pm
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
April 20, 2011 at 2:31 pm
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.
April 20, 2011 at 2:40 pm
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)
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply