sqlt-sqlsql-server-2005

deleting foreign key child record error 547

I have a Table Task with TaskId as Primary Key. And another table TimeSheet with TaskId as foreign key. I created foreign key relation like this 

ALTER TABLE TimeSheet ADD CONSTRAINT fk_TimeSheet_TaskId FOREIGN KEY(TaskId)
REFERENCES Task (TaskId)
GO

Actually, if I try to delete (or update) a row in Task table it should show an error regarding foreign key relation. And if I try to delete in TimeSheet table I should not get any error.

But for me I am getting the 547 error if I try to delete or update in TimeSheet table. Nulls are allowed in Foreign Key column (TaskId in TimeSheet Table).

What should I do here? I want to throw error if I try to delete or Update in parent table (Task) and No error if I try to delete or update in child table (TimeSheet).

Solution

  • If we use FOREIGN KEY:

    Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. Not vice-versa.

    If you want to work as per your question try to use trigger.