1

I have a process that adds some data to a database. Some of the columns are keys. I need to be able to delete the row once the process runs (so I can run it again). However Im getting the following error:

Msg 547, Level 16, State 0, Line 1 The DELETE statement conflicted with the REFERENCE constraint "FK_InviteConfiguration_Invite". The conflict occurred in database "Unilever", table "dbo.InviteConfiguration", column 'InviteID'. The statement has been terminated.

I see that there are some keys set with references between the tables how do I just force the deletion anyway?

Exitos
  • 29,230
  • 38
  • 123
  • 178
  • 1
    What flavor of RDBMS? Looks like SQL Server, but please specify. – Paul Sasik Oct 26 '11 at 15:44
  • 2
    Is this a symptom of another issue? You have records in `InviteConfiguration` that reference records in `Invite`. Why are you attempting to delete the referenced records? Should the records in `InviteConfiguration` also be deleted? Are you attempting to delete the wrong records? Are the records in `InviteConfiguration` referencing the wrong records in `Invite`? If it's not a mistaked, why are the records in `InviteConfiguration` referencing records you want to delete? And what will the foreign key in `InviteConfiguration` mean after referenced records in `Invite` are deleted? – MatBailie Oct 26 '11 at 15:51

3 Answers3

2

you can alter the constraint to do ON DELETE CASCADE

Warning: this would cascade-delete all records that reference the primary key. You should judge if that's what you want.

Icarus
  • 63,293
  • 14
  • 100
  • 115
  • 2
    Note that this won't let it "Delete it anyway" but it will also delete all of the records that reference it. Use with caution. – vcsjones Oct 26 '11 at 15:46
2

I see that there are some keys set with references between the tables how do I just force the deletion anyway?

You can do this, but its probably better just to update or delete the rows in the referencing table

ALTER TABLE InviteConfiguration NOCHECK CONSTRAINT ALL

or with a slightly smaller hammer

 ALTER TABLE InviteConfiguration NOCHECK CONSTRAINT FK_InviteConfiguration_Invite
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
1

You're asking for a hack to overcome a data integrity constraint. This is not good practice (to put it mildly) and you should approach the problem differently.

The exist records that depend on the record you're trying to delete. If you do manage to break the constraints they will be orphaned and become garbage. You should either delete them also (prior to deleting the record they depend on) or simply do an update on the record that they depend on rather than deleting them. It all depends on the biz logic semantics that you're trying to implement. In any case, you should always honor the data integrity rules.

Here's an SO discussion on this topic that you might find useful. I suggest reading the post and the suggestions which include a number of valuable considerations, but not implementing any of the hacks.

Community
  • 1
  • 1
Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • Hi this is just for testing purposes I need to scrub data and repeat a process multiple times... – Exitos Oct 27 '11 at 14:35