deleting records in associated foeign key table

Hi, i need the suggestion here in very familiar db situation ..i have a main table and a primary key of that table is used in many other table as foreign key.If i am deleting a record in a main table,how do i make sure that all the corresponding record in the associated tables,where that foreign key is used, gets deleted too?What are my options?Thanks

There are a few options, but the best two, IMO, are:

Write a SQL statement that first deletes the associated records and then deletes the parent record. Imagine that you had a Categories table and a Products table, and there is a one to many relationship from Categories to Products. Now, imagine that we wanted to delete a category that has associated products. We'd first need to delete the Products - DELETE FROM Products WHERE CategoryID = @.CategoryIDToDelete - then we'd delete the category: DELETE FROM Categories WHERE CategoryID = @.CategoryIDToDelete.
|||

Thank a lot Scott for quick response...i have got multiple tables and all these table are kind of chained up with each other using multiple foreign key .i am liking the idea of 'cascade delete',I have already set up the foreign key constraints in all these table.I will read up on cascade delete,,have no idea about it rightnow...

|||

Scott is correct on the two main options. Given that this question is a "newbie" question, it's probably best to clarify that the first option is to write TWO sql statements, not one - a delete for each table.

|||

David, do you mean,two sql statements(or as many delete as reqd for linked up tables) within one stored procedures or two(or multiple depends?) separate sql statements ?i am heavily using SP in my application.thanks

|||

I mean 1 delete statement per table that needs deleting. Those delete statements can be in one stored procedure.

My comments were to avoid some one new to sql trying to issue a delete statement like this (because it will never work):

delete department and employee where department_id = 5