我有以下要求:
我需要根据给定的ID从一个表中删除记录,现在该表被另一个表引用,另一个表被另一个表引用,而最后一个表也被另一个表引用,所以我有一个像这样的链:
table_1 <- table_2 <- table_3 <- table_4
我对SQL并不了解,所以我的解决方案包括使用子查询来执行此操作。
我有这样的事情:
DELETE FROM table_4
WHERE pk_of_table_3 IN
(SELECT id
FROM table_3
WHERE pk_of_table_2 IN
(SELECT id FROM table 2 WHERE pk_of_table_1 = ?
)
)
因此,这将从表4中清除引用表3中目标记录的记录。
对于table_3,我将执行以下操作:
DELETE FROM table_3
WHERE pk_of_table_2 IN
(SELECT id FROM table_2 WHERE pk_of_table_1 = ?)
现在,我转到table_2:
DELETE FROM table_2 WHERE pk_of_table_1 = 5;
因此,最后它使我有可能从table_1清除必要的记录,因为它不受任何约束。
我想问一下这似乎是可行的解决方案,是否有更好的方法可以做到这一点?
使用链接DELETE语句的公用表表达式来执行此操作:
with delete_t1 as (
delete from table_1
where pk = 42
returning pk
), delete_t2 as (
delete from table_2
where pk_of_table_1 in (select pk from delete_t1)
returning pk
), delete_t3 as (
delete from table_3
where pk_of_table_2 in (select pk from delete_t2)
returning pk
)
delete from table_4
where pk_of_table_3 in (select pk from delete_t3);
如果您总是这样,考虑将外键约束定义为on delete cascade
,则只需从table_1中删除即可,其余的将由Postgres负责。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句