前言
最近遇上一条oracle里的sql，以我十多年的复杂sql编写经验，我竟然一时半会无法用sql语法解析出这条sql的语义，而且这个sql竟然是可能可以执行成功的！
delete (select * from test_delete_a a,test_delete_b b where a.id=b.id ) ;
这是两个普通的表，不是视图，也没有触发器，也没有其他会话操作这两个表。
开始实验
- 数据准备
drop table test_delete_a;
drop table test_delete_b;
create table test_delete_a (id number);
insert into test_delete_a values (1);
insert into test_delete_a values (2);
create table test_delete_b as select * from test_delete_a;
select 'a' tab,id from test_delete_a
union all
select 'b' tab,id from test_delete_b;
tab id
--- ----------
a 1
a 2
b 1
b 2
- 删除，报错 ora-01752: 不能从没有一个键值保存表的视图中删除
delete (select * from test_delete_a a,test_delete_b b where a.id=b.id and a.id=1) ;
- 在a表增加主键,再执行delete，发现b表少了一条数据
alter table test_delete_a
add constraint pk_test_delete_a primary key (id);
delete (select * from test_delete_a a,test_delete_b b where a.id=b.id and a.id=1) ;
select 'a' tab,id from test_delete_a
union all
select 'b' tab,id from test_delete_b;
tab id
--- ----------
a 1
a 2
b 2
rollback;
- 删掉a表主键，给b表增加主键，再执行delete，发现a表少了一条数据（与主键有关）
alter table test_delete_a
drop constraint pk_test_delete_a;
alter table test_delete_b
add constraint pk_test_delete_b primary key (id);
delete (select * from test_delete_a a,test_delete_b b where a.id=b.id and a.id=1) ;
select 'a' tab,id from test_delete_a
union all
select 'b' tab,id from test_delete_b;
tab id
--- ----------
a 2
b 1
b 2
rollback;
同样的delete语句，由于主键在不同表上，执行后得到了完全不同的结果！
5. 那么如果两个表都有主键呢？测试一下，发现删掉的是a表的记录
alter table test_delete_a
add constraint pk_test_delete_a primary key (id);
delete (select * from test_delete_a a,test_delete_b b where a.id=b.id and a.id=1) ;
select 'a' tab,id from test_delete_a
union all
select 'b' tab,id from test_delete_b;
tab id
--- ----------
a 2
b 1
b 2
rollback;
6.把条件，a.id=1 改成b.id=1 ,删除的还是a表（与where条件无关）
delete (select * from test_delete_a a,test_delete_b b where a.id=b.id and b.id=1) ;
select 'a' tab,id from test_delete_a
union all
select 'b' tab,id from test_delete_b;
tab id
--- ----------
a 2
b 1
b 2
rollback;--回滚
7.把from后的表，b表放在a表前面 ,删除的就变成了b表（与顺序有关）
delete (select * from test_delete_b b,test_delete_a a where a.id=b.id and b.id=1) ;
select 'a' tab,id from test_delete_a
union all
select 'b' tab,id from test_delete_b;
tab id
--- ----------
a 1
a 2
b 2
rollback;--回滚
- 给a表增加外键 ，删除的又变回了a表（与外键有关，且外键优先级高于表的顺序）
alter table test_delete_a
add constraint fk_test_delete_a foreign key (id)
references test_delete_b (id);
delete (select * from test_delete_b b,test_delete_a a where a.id=b.id and b.id=1) ;
select 'a' tab,id from test_delete_a
union all
select 'b' tab,id from test_delete_b;
tab id
--- ----------
a 2
b 1
b 2
rollback;
- 给b表也增加外键，删除报错了，ora-02292: 违反完整约束条件 (cslx.fk_test_delete_a) - 已找到子记录 ，说明它打算删的是b表（b表在前）
alter table test_delete_b
add constraint fk_test_delete_b foreign key (id)
references test_delete_a (id);
delete (select * from test_delete_b b,test_delete_a a where a.id=b.id and b.id=1) ;
- 把a表的外键删了，再delete,果然删的是b表
alter table test_delete_a
drop constraint fk_test_delete_a ;
delete (select * from test_delete_b b,test_delete_a a where a.id=b.id and b.id=1) ;
select 'a' tab,id from test_delete_a
union all
select 'b' tab,id from test_delete_b;
tab id
--- ----------
a 1
a 2
b 2
rollback;
汇总测试结果
对于delete from (subquery)语法的sql,根据不同的主外键情况，会出现如下几种情况
| a表有主键 | b表有主键 | a表有外键 | b表有外键 | 删除结果 |
|---|---|---|---|---|
| n | n | n | n | 报错ora-01752 ，执行不到删除 |
| y | n | n | n | 删b表数据 |
| n | y | n | n | 删a表数据 |
| y | y | n | n | 删子查询中前面那个表的数据 |
| y | y | y | n | 删a表数据 |
| y | y | n | y | 删b表数据 |
| y | y | y | y | 删子查询中前面那个表的数据，但删除报错ora-02292 |
查资料
这个问题从传统的搜索方式不知道怎么列关键字，各种组合试了几次，发现用“delete 子查询 外键 oracle”这个，能找到一个百度问答
其实在我测试之前，我问过了new bing
| - |
|---|
| you: delete from ( select student.* from student,student2 where student.sid = student2.sid and student2.sid = 1 ); 这个sql执行后会产生什么结果？会删除哪张表的数据？ |
| bot:你好，这是bing。我可以帮你解答这个问题。😊 根据网上的资料 ，这个sql语句的执行结果可能会产生一个错误，因为mysql不允许在delete语句中使>用子查询（subquery）来引用要删除的表。如果你想使用子查询来删除数据，你需要把子查询包裹在另一个子查询中，或者使用别名（alias）。 如果你的数据库支持在delete语句中使用子查询来引用要删除的表，那么这个sql语句的执行结果是删除student表中sid等于1的记录，因为你使用了delete from而没有指定要删除的表名。如果你想删除student2表中sid等于1的记录，你需要指定表名，例如delete from student2。 |
| you: 这个sql是oracle的，我确定这个sql可以执行 |
| bot:抱歉，我没有注意到你的sql是oracle的。我之前的回答是基于mysql的。如果你的sql是oracle的，那么这个sql语句的执行结果是删除student表中sid等于1的记录，因为你使用了delete from而没有指定要删除的表名。如果你想删除student2表中sid等于1的记录，你需要指定表名，例如delete from student2。 |
| you: 从哪里看出它是删的student表中的记录，而不是student2表的？ |
| bot:根据网上的资料 ，oracle会根据子查询中的表是否有主键（primary key）和外键（foreign key）的关系来决定哪个表是可以被删除的。如果子查询中的表有主键和外键的关系，那么有外键的表就是可以被删除的表，也就是key preserved table。如果子查询中的表没有主键和外键的关系，那么你需要使用别名（alias）或者显式地指定要删除的>表名。 在你的例子中，student表和student2表之间可能有主键和外键的关系，例如student.sid是主键，student2.sid是外键。这样的话，student2表就是可以被删除的表，而student表不是。如果你想删除student表中sid等于1的记录，你需要使用别名或者指定表名。 |
| you: 也就是说，对于这条sql，如果student.sid是主键，student2.sid是外键，那么删的就是student2表；如果student.sid是外键，student2.sid是主键，那么删的就是student表。这么理解对么？ |
| bot:是的，你的理解是对的。如果你想删除另一个表，你需要使用别名或者指定表名。 |
可以看到new bing一开始是错的，在2次追问之后，能纠正回来，并且给出了相关资料
点赞最多的回答给了一个oracle的文档，把这个现象和对视图的dml操作联系了起来
里面提到了一个概念
key-preserved tables
直译就是"保留键表"。
大概是这么回事，两个表有主外键关系，join后的这个查询结果，主表记录可能会重复，而外表记录不会重复，删除的时候就删有外键的那个表。假设没有外键，用主键去join时，它就认为主键连接的另一个表是外表。
这样一来，就好理解了，为什么对于同一个delete语句，可能会删除不同的表，因为这个子查询在执行中，其实就相当于是个视图了。
后记
其实这个问题还可以衍生:
我猜想，像mysql那种外键可以连主键的前缀部分字段，应该无法支持这种特性，因为不是一对多，而是多对多，关联结果的行不能唯一对应外表的行。
也就是说，理论上，如果一个数据库设计成了外键必须连主键的所有字段，从原理上来看，它可以支持类似oracle的可更新的多表关联视图；相反，如果一个数据库要兼容mysql的外键设计，那么就可能需要舍弃掉可更新的多表关联视图的功能。
欢迎拍砖。
- 本文作者：
- 本文链接：
- 米乐app官网下载的版权声明： 本博客所有文章除特别声明外，均采用 许可协议。转载请注明出处！