November 26, 2012 at 2:43 pm
Hey all,
I am running into an issue where rows are appearing in a table where the primary key is not present in a base table. A foreign key constraint decrees that all the values present in a column of the dependent table is present in the base table. There are a bunch of rows in the dependent table that have values that aren't in the base table.
There are triggers and such to ensure that this constraint isn't violated, and yet it seems to have been violated at most of our replicating clients. I suspect that the issue is introduced when replication is down, but I can't figure out how. Rows are never deleted from any of these tables, so you figure if the rows weren't present on one server they'd be present on the other. Any insight as to how this could happen?
I would appreciate any advice that anyone can provide.
Thanks.
November 26, 2012 at 2:49 pm
SQLUserC (11/26/2012)
Hey all,I am running into an issue where rows are appearing in a table where the primary key is not present in a base table. A foreign key constraint decrees that all the values present in a column of the dependent table is present in the base table. There are a bunch of rows in the dependent table that have values that aren't in the base table.
There are triggers and such to ensure that this constraint isn't violated, and yet it seems to have been violated at most of our replicating clients. I suspect that the issue is introduced when replication is down, but I can't figure out how. Rows are never deleted from any of these tables, so you figure if the rows weren't present on one server they'd be present on the other. Any insight as to how this could happen?
I would appreciate any advice that anyone can provide.
Thanks.
Well if you are using triggers to enforce RI then you don't actually have any foreign keys. The best thing would be to setup actual foreign keys.
I suspect you may be correct that replication errors could be the cause of the data issues. With proper RI in place this wouldn't happen.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply