使用一个 SQL 脚本删除子行和父行

Delete a child and a parent row with one SQL script

提问by WowBow

Instead of deleting the child row and then writing another sql statement to delete the parent row I wanted to use one statement which will do both. FYI: we use Oracle database.

而不是删除子行然后编写另一个 sql 语句来删除父行，我想使用一个可以同时执行这两个操作的语句。仅供参考：我们使用 Oracle 数据库。

Update:I dont have a privilege to do DELETE ON CASCADE

更新：我无权在级联上执行 DELETE

采纳答案by Albin Sunnanbo

Define your foreign keys with cascading deletes. Then you only need to delete the "parent" row.

使用级联删除定义外键。然后你只需要删除“父”行。

回答by grokster

delete from 
(
select * from parent join child using (id)
where id = 1
)

WARNING! Will only delete where both parent AND child rows exist. Will NOT delete parents without children

警告！只会删除父行和子行都存在的地方。不会删除没有孩子的父母

回答by faghani

After some really bad experiences with this problem on a relatively big and extremely critical database, I decided to make a Silver bulletfor it! because I couldn't find any! Actually, none of the solutions/answers in this thread meet the problem's needs.

在一个相对较大且极其关键的数据库上遇到这个问题的一些非常糟糕的经历之后，我决定为它制作一个银弹！因为我找不到任何！实际上，该线程中的任何解决方案/答案都不能满足问题的需求。

See CASCADELETErepo on my github.

请参阅我的 github 上的CASCADELETE 存储库。

回答by Adam Musch

You can only do it badly - i.e., using triggers.

你只能做得很糟糕 - 即，使用触发器。

create table parent
(pid number,
   constraint parent_pk
     primary key (pid)
     using index
);

create table child
  (cid number,
   pid number,
   constraint child_pk
     primary key(cid)
     using index,
   constraint child_fk
     foreign key (pid)
     references parent (pid)
  );

create index child_fk on child (pid);

create trigger fake_delete_cascade
before delete on parent
for each row
begin
  delete from child where pid = :old.pid;
end;
/

insert into parent values (1);
insert into child values (1,1);
commit;
select count(*) from child;
delete from parent where pid = 1;
select count(*) from child;

回答by Justin Cave

If you always want to delete the children when you delete a parent row, you can declare the foreign key constraint so that Oracle does the child delete automatically

如果在删除父行时总是想删除子行，则可以声明外键约束，以便Oracle自动删除子行

create table parent (
  parentID number primary key,
  parentData varchar2(100)
);

create table child (
  childID number primary key,
  parentID number references parent( parentID ) on delete cascade,
  childData varchar2(100)
);

for example, will declare a parent table and a child table and automatically delete the child rows when you delete the parent row. If you don't want that sort of thing to be enforced automatically or you don't like the complexity that is added when things happen "automagically" in the background, you're probably stuck with using multiple DELETEstatements.

例如，将声明一个父表和一个子表，并在删除父行时自动删除子行。如果您不希望自动执行这类事情，或者您不喜欢在后台“自动”发生事情时增加的复杂性，那么您可能会坚持使用多个DELETE语句。

回答by Rapha?l Althaus

Another (boring way, we have this in a database which, for unknown reason, don't use foreign keys as constraints - yes yes) to do this would be to create a trigger after (or before) delete.

另一种（无聊的方式，我们在一个数据库中有这个，由于未知的原因，不使用外键作为约束 - 是的，是的）这样做是在删除之后（或之前）创建一个触发器。

You'll have to write another delete query, but just in the trigger.

您必须编写另一个删除查询，但只是在触发器中。

But if you can't put delete cascade, I'm not sure you can add triggers...

但是如果你不能放删除级联，我不确定你可以添加触发器......

回答by Katie.Sun

In case it helps anyone else, I just wrote a PLSQL script to do this for all foreign key constraints in a table with some help from this Stackoverflow question. Hope it helps.

如果它对其他人有帮助，我只是编写了一个 PLSQL 脚本来对表中的所有外键约束执行此操作，并借助Stackoverflow question 的一些帮助。希望能帮助到你。

DECLARE
  CURSOR constraint_cursor IS SELECT *
                              FROM (SELECT a.table_name,
                                           a.constraint_name,
                                           a.column_name,
                                           c_pk.table_name r_table_name,
                                           b.column_name   r_column_name
                                    FROM user_cons_columns a
                                           JOIN user_constraints c ON a.owner = c.owner
                                                                        AND a.constraint_name = c.constraint_name
                                           JOIN user_constraints c_pk ON c.r_owner = c_pk.owner
                                                                           AND
                                                                         c.r_constraint_name = c_pk.constraint_name
                                           JOIN user_cons_columns b ON C_PK.owner = b.owner
                                                                         AND
                                                                       C_PK.CONSTRAINT_NAME = b.constraint_name AND
                                                                       b.POSITION = a.POSITION
                                    WHERE c.constraint_type = 'R'
                                      and c_pk.owner = 'YOUR SCHEMA HERE') tbl;
  sql_statement VARCHAR2(2048) := NULL;
  tab_row       constraint_cursor%rowtype;
BEGIN
  OPEN constraint_cursor;
  FOR i in 1..80 LOOP
    FETCH constraint_cursor into tab_row;
    EXECUTE IMMEDIATE 'ALTER table ' || tab_row.table_name || ' drop constraint ' || tab_row.constraint_name;
    EXECUTE IMMEDIATE 'ALTER table ' || tab_row.table_name || ' add constraint ' || tab_row.constraint_name || ' FOREIGN KEY (' ||
    tab_row.column_name || ') references ' || tab_row.r_table_name || '(' || tab_row.r_column_name || ') ON DELETE CASCADE ';
  end loop;
  close constraint_cursor;
end;