在关系型数据库中,跨多表删除相关记录可以通过以下几种SQL技术实现:
如果表之间设置了外键约束并指定了ON DELETE CASCADE,删除主表记录时会自动删除关联表的记录。
-- 创建表时设置级联删除
CREATE TABLE orders (
order_id INT PRIMARY KEY
);
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);
-- 删除orders表中的记录会自动删除order_items中相关记录
DELETE FROM orders WHERE order_id = 123;
MySQL支持直接从多个表中删除记录:
DELETE o, oi
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = 123;
对于不支持多表DELETE的数据库:
-- 先删除从表记录
DELETE FROM order_items
WHERE order_id IN (SELECT order_id FROM orders WHERE customer_id = 456);
-- 再删除主表记录
DELETE FROM orders WHERE customer_id = 456;
-- PostgreSQL示例
WITH orders_to_delete AS (
SELECT order_id FROM orders WHERE customer_id = 456
)
DELETE FROM order_items
WHERE order_id IN (SELECT order_id FROM orders_to_delete);
DELETE FROM orders WHERE customer_id = 456;
BEGIN TRANSACTION;
DELETE FROM order_items WHERE order_id IN (
SELECT order_id FROM orders WHERE customer_id = 456
);
DELETE FROM orders WHERE customer_id = 456;
COMMIT;
不同数据库系统对多表删除的支持程度不同,请根据您使用的具体数据库选择合适的方法。