Different results while deleting data in different Environments

Question

Post reply

Different results while deleting data in different Environments

sqlnaive

SSCoach

Points: 17437
More actions
June 15, 2011 at 5:42 am

#96410

I am deleting records from one table which has Foreign Key dependency on another table. On two environments, its working fine. In one environment it's giving error saying "The DELETE statement conflicted with the REFERENCE constraint "TEST_TABLE_FK1". I checked the scripts from SSMS in all the three environments and did not found "ON DELETE CASCADE" in any of the Environments.

Viewing 8 posts - 1 through 7 (of 7 total)

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

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

Are there any foreign key dependencies on the table your deleting from?

DECLARE @tblname NVARCHAR(255)

SET @tblname = 'YOUR TBL NAME HERE'

SELECT DISTINCT OBJECT_NAME(parent_object_id)

FROM sys.foreign_keys

WHERE referenced_object_id = OBJECT_ID(@tblname)

_____________________________________________________________________
- Nate

@nate_hughes

sqlnaive SSCoach Points: 17437 More actions · Answer 2

I am getting these errors in three tables. Say TabA, TabB, TabC. PK of TabA is referenced by FK in TabB and PK in TabB is referenced by FK in TabC. The columns in foreign key between both pairs are not entirely common.

RP_DBA SSCertifiable Points: 5116 More actions · Answer 3

Which table (A, B or C) are you deleting from?

_____________________________________________________________________
- Nate

@nate_hughes

sqlnaive SSCoach Points: 17437 More actions · Answer 4

All of them, but getting errors in all cases. Interestingly, same definition defined in other environments as well but deletion happening successfully.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

are you trying to purge the data completely or is it a selective delete?

and a really stupid question, but no-one has asked the obvious.

are you deleteing the data in the same order in all cases - ie table c first and table a last....

MVDBA

sqlnaive SSCoach Points: 17437 More actions · Answer 6

Nope, Its on selective basis. And Yes, its in same order in each environment.

RP_DBA SSCertifiable Points: 5116 More actions · Answer 7

So it's delete from TabC where ..., delete from TabB where..., and then delete from TabA where...?

If you're sure it's not a data issue, you might try putting GO statements or semicolons after each DELETE statement.

Brief overview of the GO Command and the Semicolon Terminator:

http://www.sqlservercentral.com/articles/SQL+Puzzles/thegocommandandthesemicolonterminator/2200/

_____________________________________________________________________
- Nate

@nate_hughes