删除表中的行,具体取决于在另一个表中删除的行 [英] Delete table rows depending upon rows deleted in another table
问题描述
我有两个表 t1
和 t2
.我这样删除 t2
的某些行:
I have two tables t1
and t2
. I am deleting certain rows of t2
this way:
delete from t2 where expiry < NOW();
t1
和 t2
共有一个列 id
.当执行以上语句时,我还想删除 t1
中的对应行(如果有的话)(可能没有).
There is a column id
common to both t1
and t2
. When the above statement is executed, I also want to delete corresponding rows in t1
if any (there might be none).
这怎么办?
是否可以为这两个操作编写一个查询?
How can this be done?
Is it possible to write a single query for both these operations?
推荐答案
我至少看到了三种好的方法-取决于问题中遗漏的更多细节.我以您的示例为基础:
I see at least three good ways - depending on further details missing in your question. I build on your example:
-
t2
是母表 -
t1
子
试验台:
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中的相应行将被自动删除:
If you delete rows from t2, corresponding rows from t1 are deleted automatically:
DELETE FROM t2 WHERE id IN (1,2,3,4,5);
-- rows 2,4 in `t1` are deleted automatically
要将这样的外键约束添加到现有表中:
To add such a foreign key constraint to existing tables:
ALTER TABLE t1 ADD CONSTRAINT t1_id_fkey FOREIGN KEY (id)
REFERENCES t2 (id) ON DELETE CASCADE;
当然,外键需要 t1.id
上的唯一键或主键.但是您可能会遇到这种情况.
Of course, a foreign key requires a unique or primary key on t1.id
. But you probably have that in a scenario like that.
如果 t2.id
中的值不是唯一的,或者如果 t1.id
中的值违反外键约束,则可以在AFTER DELETE之后创建触发器代替.
If the values in t2.id
are not unique, or if you have values in t1.id
violating a foreign key constraint, you could create a trigger AFTER DELETE instead.
触发功能:
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();
您还可以实施规则.但是我发现触发器通常更易于处理.
You could also implement a RULE. But I find triggers generally easier to handle.
请原谅我最后给出最简单的答案.无论使用哪种方法,只要您使用的是PostgreSQL 9.1或更高版本,
Forgive me for giving the simplest answer last. This works, no matter what - provided you are on PostgreSQL 9.1 or later:
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;
这篇关于删除表中的行,具体取决于在另一个表中删除的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!