没有Alter命令，删除Cascade行。

I have to delete a product and its images. Images are in seperate table and productId is acting as foreign key. Here is the single queries to do so.

我必须删除一个产品和它的图像。图像在分离表中，productId作为外键。下面是要执行的单个查询。

string deleteImages = @"DELETE FROM [ProductsImages] WHERE ProductId IN (SELECT ProductId FROM [Products] WHERE ProductId = @ProductId)";
string deleteProduct = @"DELETE FROM [Products] WHERE ProductId = @ProductId";
       db.ExecuteNonQuery(deleteImages);
       db.ExecuteNonQuery(deleteProduct);

I am not getting the way to avoid writing 2 different queries, Is there any help to delete cascade without alter command?

我没有得到避免编写两个不同查询的方法，是否有帮助删除级联而不改变命令?

3 个解决方案

#1

If you don't want to change the index, you can avoid two round trips by performing multiple operations in one command:

如果您不想更改索引，可以通过在一个命令中执行多个操作来避免两次往返:

string deleteStuff = @"
  DELETE FROM [ProductsImages] WHERE ProductId = @ProductId;
  DELETE FROM [Products] WHERE ProductId = @ProductId;"
db.ExecuteNonQuery(deleteStuff);

#2

This is something you can define in the database. Just define the foreign key relationship between Products and ProductsImages as "ON DELETE CASCADE". Deleting the product will then automatically delete all associated ProductImages.

这是可以在数据库中定义的。只需将产品和产品之间的外键关系定义为“ON DELETE CASCADE”。删除产品将自动删除所有相关的产品时间。

#3

DELETE p.*, i.*
FROM Products p
LEFT JOIN ProductImages i ON p.ProductId = i.ProductId
WHERE p.ProductId = @ProductId

EDIT Not supported by SQL Server

SQL Server不支持的编辑

#1