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)?
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.
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!
@categoryid is part of the proc stored procedure
delete from products where categoryid = @categoryid
delete from categories where categoryid = @categoryid
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.
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
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.