|
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?
(comments are locked)
|
|
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. 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 '12 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 '12 at 02:58 PM
Grant Fritchey ♦♦
(comments are locked)
|
|
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 +1 for soft delete
Feb 22 '12 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 '12 at 02:22 PM
Sanfoor
(comments are locked)
|
|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
I think removing the relationship is best solution thanks anyway
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