0

I am stuck in strange problem. I have a table say "Location" with two FKs i.e. Geographic, TelephoneNumber.

Model wise both has exactly same relationship i.e. allow null true for Location.telephonenumberid and Location.GeographicId however when I remove telephonenumber it works perfectly fine and new number gets added. However same thing is failing for GeographicId it always fails with error "The primary key value cannot be deleted because references to this key still exist." I am assuming setting allow null to telephonenumberid and geographicid in location table and defining relationship as 0-* - 1 should work like optional relationship but its not happening.

Any help would be appreciable

Regards Rashmi

Rashmi
  • 239
  • 3
  • 9

1 Answers1

0

Did you mean, If you delete a row from parent, automatically the refernce key in child should change to NULL. If yes, try the below code

ALTER TABLE child_table
ADD CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE SET NULL;

I hope this problem is already solved in stackoverflow. You can refer How to delete a record with a foreign key constraint?

Use cascade delete

Refer : http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

Community
  • 1
  • 1
Gisha Ajeesh
  • 82
  • 3
  • Hello Gisha Thank you for your reply. I do not want to delete parent (location) entity, I just want to set FK to null and delete child entity (TelephoneNumber). cascade delete works in reverse way like if i delete location it will delete telephone which I am already using. – Rashmi Jul 01 '15 at 10:07
  • I have updated the reply. Kindly check if its the scenario which you have mentioned – Gisha Ajeesh Jul 01 '15 at 11:44
  • Thanks it was useful! – Rashmi Jul 01 '15 at 14:20