Clever Geek Handbook

Dropping from two tables at the same time?

I am using asp.net and sql server. I have 2 tables: categories and products. in the product table I have categoryId as FK. what i want to do: when i remove a category from the category table, i want all products from that category to be removed in the products table. how can this be done (I prefer to store the procedure, but not its mandate)?

+2


source to share


6 answers


You can tell FK is using DELETES CASCADE. Otherwise, you will need to delete all products in the category first and then delete the category.



+2


source


If you can customize the schema, SQL Server supports cascading deletes . With this FK limitation, you get this effect with a single removal to the category. Not everyone loves cascading deletes, mind you!



+2


source


@categoryid is part of the proc stored procedure

delete from products where  categoryid = @categoryid
delete from categories where categoryid = @categoryid
+1


source


There are many ways to do this. I would set deletes to "Cascade" on your foreign keys in SQL. Let SQL handle it for you, that's what it's good at.

+1


source


if you want to do it with a stored procedure

delete from Categories where categoryId=@categoryId 
delete from Products where categoryId = @categoryId

if it always happens. If you remove anything from the category table, it should remove from the products. my option is DELETE CASCADE.something like this

ALTER TABLE dbo.Products   
WITH CHECK ADD  CONSTRAINT FK_Products_Categories FOREIGN KEY([categoryId])
REFERENCES dbo.Categories([categoryId])
ON DELETE CASCADE

so when you remove from the categories table, it will automatically remove from the products table also

eg: delete from dbo.Categories where categoryId =@categoryId 

   no use of writing 

   delete from Products where categoryId = @categoryId
+1


source


You can do this by creating a relationship between tables in the "Diagrams" section of the database (assuming MS SQL 2005/2008) or a relationship button at the top of the screen (SQL 2000).

After creating a one-to-many relationship with cascading delete, the query can be as simple as:

delete from Categories where CategoryId = XX

This will automatically remove all products associated with the category.

0


source






More articles:


All Articles