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'

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

5 Answers 5


You don't need to use the on delete cascade. Somebody (the schema design author) had made sure you cannot delete a 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 constraints and ask him how to properly delete the records you're trying to delete, in the correct order and taking the proper precautions to keep the database consistent.


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>)

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.


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'

and this is to enable it

ALTER TABLE dbo.M02ArticlePersons CHECK CONSTRAINT FK_M02ArticlePersons_M06Persons

Hope this will work

  • 4
    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, 2013 at 20:19

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.


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)
                         @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
       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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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