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?
asked Feb 22 '12 at 02:27 PM in Default
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.
answered Feb 22 '12 at 02:32 PM
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
answered Feb 22 '12 at 03:18 PM
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.
answered Feb 23 '12 at 02:44 PM
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.
answered Feb 23 '12 at 10:02 AM