CODEWITHSUNDEEP
sql
Amazing User
Amazing User

Reputation: 3563

Conflict of the DELETE statement with the restriction REFERENCE

When I try to excecute this code, I'm getting error:

Conflict of the DELETE statement with the restriction REFERENCE "FK_Options_users". The conflict occurred in database "WINTOUR", table "PrintForm.Options", column 'user_code'

Can't understand why and how to fix that.

declare

  @USER_CODE int;

select

  @USER_CODE = 24;

delete from Settings.Items where user_code =   @USER_CODE

delete from usnet where code =   @USER_CODE

delete from usgroups where usercode =   @USER_CODE

delete from users where code =   @USER_CODE

Upvotes: 0

Views: 100

Answers (3)

Deepshikha
Deepshikha

Reputation: 10264

It seems like Foreign Key constraint exists between the user_code column in PrintForm.Options and the code/user_code column in given tables.

If you try to delete all the data in given tables an error will occur as the user_code column in PrintForm.Options reference the data in the any one of the table from which yor are deleting the data.

To resolve the issue you should either drop and recreate the constraint FK_Options_users or delete the data from child table PrintForm.Options that the Foreign Key references ie where user_code = 24.

Upvotes: 1

Rob
Rob

Reputation: 2656

You have a foreign key relation with one of the rows you are trying to delete. That means that the key is used in another table. You must delete in the correct order so that does not happen.

You are missing a delete for the elements in specified in the error. So in Database WINTOUR in the table PrintForm.Options the use_code is a foreign key to the usercode you are deleting.

so you need to add 

 delete from PrintForm.Options where user_code = @USER_CODE

probably right before or after Settings.Items.

Upvotes: 1

Martin Milan
Martin Milan

Reputation: 6390

It looks to me like you are removing user 24, but the PrintForm.Options table has an entry that is still using it, and were it to be deleted, the foreign key would no longer be satisifed.

Have you perchance missed "Printform.Options" from the list of delete queries?

Upvotes: 1

Related Questions