sql - 根据在另一个表中删除的行删除表行

Question

我有两个表 t1 和 t2。我正在以这种方式删除 t2 的某些行:

delete from t2 where expiry < NOW();

t1 和 t2 都有一个 id 列。执行上述语句时，我还想删除t1中的相应行(如果有的话)(可能没有)。

如何做到这一点？
是否可以为这两个操作编写一个查询？

Answer 1

我看到至少三种好方法 - 取决于您的问题中缺少的更多细节。我以你的例子为基础:

• t2是母表
• t1 child

1。 Foreign key constraint使用级联删除

测试台:

CREATE TABLE t2 (
  id serial PRIMARY KEY  -- primary or unique key needed
, expiry timestamp
);

CREATE TABLE t1 (
  id int references t1(id) ON DELETE CASCADE  -- ON UPDATE CASCADE, too?
);

填充表格:

INSERT INTO t2(expiry)
SELECT (now() + g * interval '1h')
FROM   generate_series(1, 10) g;   -- 10 arbitrary rows

INSERT INTO t1(id)
SELECT id FROM t2 WHERE id%2 = 0;  -- pick even numbers for t1

SELECT * FROM t1;

如果您从 t2 中删除行，则 t1 中的相应行会自动删除:

DELETE FROM t2 WHERE id IN (1,2,3,4,5);
-- rows 2,4 in `t1` are deleted automatically

向现有表添加这样的外键约束:

ALTER TABLE t1 ADD CONSTRAINT t1_id_fkey FOREIGN KEY (id)
REFERENCES t2 (id) ON DELETE CASCADE;

当然，外键需要t1.id 上的唯一键或主键。但在这样的情况下，您可能会遇到这种情况。

2。 Trigger AFTER DELETE

如果 t2.id 中的值不是唯一的，或者如果 t1.id 中的值违反外键约束，则可以在删除后创建触发器相反。

触发函数:

CREATE OR REPLACE FUNCTION t2_delaft
  RETURNS trigger 
  LANGUAGE plpgsql AS
$func$
BEGIN
   DELETE FROM t1
   WHERE  t1.id = OLD.id;

   RETURN NULL;  -- AFTER trigger can return NULL
END
$func$

触发器:

CREATE TRIGGER delaft
AFTER DELETE ON t2
FOR EACH ROW EXECUTE PROCEDURE t2_delaft();

您还可以实现 RULE .但我发现触发器通常更容易处理。

3。 Data modifying CTE

原谅我最后给出最简单的答案。无论如何，这都有效 - 只要您使用的是 PostgreSQL 9.1 或更高版本:

WITH x AS (
    DELETE FROM t1
    WHERE  id IN (1,2,3,4,5)
    RETURNING id
    )
DELETE FROM t2
USING  x
WHERE  t2.id = x.id;