locked
ON DELETE CASCADE RRS feed

  • Question

  • Helle people. The question is:

    I have two tables: Directories and SubDirectories. SubDirectories table has foreign key for DirectoryId. Now if I define this key with ON DELETE CASCADE attribute then in case of I delete a Directory record, all SubDirectory records will also be deleted. But if I have only one table Directories that have field named ParentId and in this field I save an Id of parent direcory. Can I define a parentId field as a foreign key to the same table and define it with ON DELETE CASCADE? Will it work too? Thanks

     

    Friday, March 7, 2008 8:49 AM

Answers

  • hmm.. I havent tried this scenario but what I think is you can not use same tables primary key as a foreign key for another field...

     

    ... but you can achive your goal by implementing trigger (on delete) on your table.. say when you delete the records from the table.. delete all the child records of that ID .. (important.. set nested triggers option before implementing such trigger.. see more detail on BOL).

     

    Friday, March 7, 2008 9:28 AM