Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to delete all users but getting the error:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_M02ArticlePersons_M06Persons". The conflict occurred in database "workdemo.no", table "dbo.M02ArticlePersons", column 'M06PersonId'.
The statement has been terminated.

The query:

DELETE FROM [workdemo.no].[dbo].[M06Persons] 
WHERE ID > '13'
GO

Seems I need to use on delete cascade; but I'm stuck.

share|improve this question

5 Answers 5

You don't need to use the on delete cascade. Somebody (the schema design author) had made sure you cannot delete an person that is still referenced by an article. It succeeded, you were just trying to do this and was blocked, kudos to the designer.

Now go and talk with that somebody that designed the schema and knows the cosntrainst and ask him how to do properly delete the records you're trying to delete, in the correct order and taking the proper precautions to keep the database consistent.

share|improve this answer

You have two real choices here, you can disable constraints on the table. This usually not a great idea as you can end up with a bad data condition if you're messing with data that relates to other tables, but not know the full extent of your schema and it may suit your purposes:

ALTER TABLE [workdemo.no].[dbo].[M06Persons] NOCHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons]

Remember to turn the constraint back on after the delete with

ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH CHECK CHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons]

The second choice would be to drop and re-add the constraint with the ON DELETE CASCADE option using:

ALTER TABLE [workdemo.no].[dbo].[M06Persons] DROP CONSTRAINT [FK_M02ArticlePersons_M06Persons]

ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH NOCHECK ADD CONSTRAINT [FK_M02ArticlePersons_M06Persons] FOREIGN KEY(M06PersonId)
REFERENCES <parent table here> (<parent column here>)
ON DELETE CASCADE

Based on your FK name it looks like your parent table is M02ArticlePersons and the parent column is M06Persons.

If you did not author this schema please try to consider why the constraints may be present, and understand that violating them in this manner may have unintended side effects.

share|improve this answer
    
thank you very much, sql is hard steep leaerning curve –  Darkmage Apr 4 '13 at 12:33

There is another manual option too:

You can go to the child table and delete the child rows referenced by the parent key. Then you can delete the parent row. This is essentially what the cascade delete does. This way, you do not have to drop/recreate/alter your constraints.

share|improve this answer

This little code will help for any table that you want to delete records from. It takes care of referential integrity as well ...

Below code will generate DELETE statements .. Just specify the schema.table_Name

Declare @sql1 varchar(max)
      , @ptn1 varchar(200)
      , @ctn1 varchar(200)
      , @ptn2 varchar(200)
      , @ctn2 varchar(200)
--
SET @ptn1 = ''
--
SET @ctn1 = ''
--
SET @ptn2 = ''
--
SET @ctn2 = ''
--
SELECT @sql1 = case when (@ptn1 <> OBJECT_NAME (f.referenced_object_id)) then
                         COALESCE( @sql1 + char(10), '') + 'DELETE' + char(10) + ' ' + OBJECT_NAME (f.referenced_object_id) + ' FROM ' + OBJECT_NAME(f.parent_object_id) + ', '+OBJECT_NAME (f.referenced_object_id) + char(10) +' WHERE ' + OBJECT_NAME(f.parent_object_id) + '.' + COL_NAME(fc.parent_object_id, fc.parent_column_id) +'='+OBJECT_NAME (f.referenced_object_id)+'.'+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
                    else
                         @sql1 + ' AND ' + OBJECT_NAME(f.parent_object_id) + '.' + COL_NAME(fc.parent_object_id, fc.parent_column_id) +'='+OBJECT_NAME (f.referenced_object_id)+'.'+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
                    end + char(10)
     , @ptn1 = OBJECT_NAME (f.referenced_object_id)
     , @ptn2  = object_name(f.parent_object_id)
FROM   sys.foreign_keys AS f
       INNER JOIN
       sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
WHERE  f.parent_object_id = OBJECT_ID('dbo.M06Persons'); -- CHANGE here schema.table_name
--
print  '--Table Depended on ' + @ptn2 + char(10) + @sql1
share|improve this answer

dbo.M02ArticlePersons table of column M06PersonId is reffered in another table. So before delete statement, disable this relationships and try again

below is for disbling the foreign key

 ALTER TABLE dbo.M02ArticlePersons NOCHECK CONSTRAINT FK_M02ArticlePersons_M06Persons

DELETE FROM [workdemo.no].[dbo].[M06Persons] 
  WHERE ID > '13'
GO

and this is to enable it

ALTER TABLE dbo.M02ArticlePersons CHECK CONSTRAINT FK_M02ArticlePersons_M06Persons

Hope this will work

share|improve this answer
    
horrible suggestion. You should NEVER disable an FK constraint unleess you are a senior dba (inwhich case you would not have written teh question above). Those constraints are there to prevent you from deleting the records. Disabling them willy nilly will give you bad data in your database. You are recommedning a worst practice not a good one. –  HLGEM Apr 4 '13 at 20:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.