使用外键从两个h2表中删除 [英] Deleting from two h2 tables with a foreign key

问题描述

我有一个h2数据库，其中有两个带有外键的表，例如

I have an h2 database with two tables with foreign keys like

CREATE TABLE master (masterId INT PRIMARY KEY, slaveId INT);
CREATE TABLE slave (slaveId INT PRIMARY KEY, something INT,
    CONSTRAINT fk FOREIGN KEY (slaveId) REFERENCES master(slaveId));

并且需要类似

DELETE master, slave FROM master m JOIN slave s ON m.slaveId = s.slaveId
    WHERE something = 43;

，即，从两个表中删除(AFAIK在MySql中起作用).因为有外键，所以我不能先从主密钥中删除.当我从从服务器删除开始时，我丢失了要从主服务器删除哪些行的信息.使用ON DELETE CASCADE会有所帮助，但我不希望每次都自动发生.我应该暂时允许吗?我应该使用临时表还是最好的解决方案?

i.e., delete from both tables (which AFAIK works in MySql). Because of the FOREIGN KEY I can't delete from the master first. When I start by deleting from the slave, I lose the information which rows to delete from the master. Using ON DELETE CASCADE would help, but I don't want it happen automatically every time. Should I allow it temporarily? Should I use a temporary table or what is the best solution?

推荐答案

没人回答，但这实际上很简单:

Nobody answers, but it's actually trivial:

SET REFERENTIAL_INTEGRITY FALSE;
BEGIN TRANSACTION;
DELETE FROM master WHERE slaveId IN (SELECT slaveId FROM slave WHERE something = 43);
DELETE FROM slave WHERE something = 43;
COMMIT;
SET REFERENTIAL_INTEGRITY TRUE;

这篇关于使用外键从两个h2表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！