x

the delete statement conflicted with the reference constraint

Hi.. I'm trying to delete one record from table(A) that has a primary key, but I'm getting on this error: "the delete statement conflicted with the reference constraint fk ....". this table has a relationship with table(B) connected through a foreign key, the delete role for this FK is "No Action" because I don't want to delete it.

how can I fix this?
more ▼

asked Feb 22, 2012 at 02:27 PM in Default

Sanfoor gravatar image

Sanfoor
36 4 4 6

I think removing the relationship is best solution thanks anyway
Feb 22, 2012 at 11:08 PM Sanfoor
This can only be confirmed by the person that put the relationship in place, or the person that told them to. It was, we must presume, put there for a good reason. Simply taking it away could bring your data into a state where it is unusable or misleading. Make sure you get advice on making this change before you do it
Feb 23, 2012 at 10:30 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Welcome to referential integrity.

Some records in B obviously relate to the record in A that you wish to delete. They would be orphaned if you did.

Determine what B record(s) use the PK from A. Then check your business rules and see what alternative relations they can have.

The system is basically working as designed.
more ▼

answered Feb 22, 2012 at 02:32 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 37

I didn't get you, what I meant is when I delete the record from table(A) I don't want to delete the child record from table(B). I don't want to put it "On delete Cascade"
Feb 22, 2012 at 02:51 PM Sanfoor
If tableA is the parent record, you can't delete it without first deleting child records in TableB. That's the whole idea behind referential integrity.
Feb 22, 2012 at 02:58 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

When you say you want to "delete" the record, do you mean you don't wish it to appear in a certain query? What the reason for wanting to "delete" it?

As the guys have said, because there is a foreign key between table A and table B you cannot delete from table A because there are one or more rows in table B that are linked to that row (via the foreign key)

Take this example, you have a address table and a person table, the person table has an address ID in it, which tells you where the person lives by linking to the ID in the address table. If you delete that address row, how would you then know where that person lives? you wouldn't. All you would have is a meaningless key that no longer exists in your address table. What the referential integrity is doing is stopping you creating these "orphaned" rows.

If you wish to suppress this row from some sort of output I would suggest adding a bit column called "deleted" or similar and setting this to one for records you wish to suppress. Then in any output queries you simply filter for records where the "deleted" column is 0. This is sometimes called "soft deleting"

Hope this helps

Thanks

Dave
more ▼

answered Feb 22, 2012 at 03:18 PM

Dave Morrison gravatar image

Dave Morrison
472 2 3 4

+1 for soft delete
Feb 22, 2012 at 04:09 PM Blackhawk-17

Thanks Dave,

but I made this child table just for show, or I can name it as a history table to know when the records has been added, edited or deleted, so I don't think I will face any issue if I remove the relation.

Thanks
Feb 23, 2012 at 02:22 PM Sanfoor
(comments are locked)
10|1200 characters needed characters left
Sanfoor, I'd urge you to take another look at this. There was clearly a reason this relationship was set up. simply removing it could cause you all kinds of unexpected problems.
more ▼

answered Feb 23, 2012 at 10:02 AM

Dave Morrison gravatar image

Dave Morrison
472 2 3 4

(comments are locked)
10|1200 characters needed characters left
If you want to keep the child records, but delete the parent record, you'll have to first break up the relation to the parent record. You can do this by setting the foreign key column to NULL in the child table. It's not the database design I'd recommend, but it would work. That way you know that any row in the child table which has a non-null foreign key column value has a corresponding record in the parent table.
more ▼

answered Feb 23, 2012 at 02:44 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1846

asked: Feb 22, 2012 at 02:27 PM

Seen: 1659 times

Last Updated: Feb 23, 2012 at 02:45 PM