Oracle之外鍵(Foreign Key)用法詳解（二）- 串聯刪除（DELETE CASCADE），oraclecascade

Oracle之外鍵(Foreign Key)用法詳解（二）- 串聯刪除（DELETE CASCADE），oraclecascade

Oracle外鍵（Foreign Key)之串聯刪除（DELETE CASCADE）

目標

樣本講解如何在Oracle外鍵中使用串聯刪除

什麼是串聯刪除（DELETE CASCADE）？

串聯刪除是指當主表(parent table)中的一條記錄被刪除，子表中關聯的記錄也相應的自動刪除。

外鍵的串聯刪除可以在建立表時定義，也可以使用ALTER TABLE文法定義。

建立表時定義串聯刪除文法：

CREATE TABLE table_name(    column1 datatype null/not null,    column2 datatype null/not null,    ...    CONSTRAINT fk_column          FOREIGN KEY (column1,column2,...column_n)          REFERENCES parent_table (column1, column2,... column_n)          ON DELETE CASECADE);

樣本：

create table tb_supplier(  supplier_id number not null,  supplier_name varchar2(50) not null,  contact_name varchar2(50),  CONSTRAINT pk_supplier PRIMARY KEY (supplier_id));create table tb_products(  product_id number not null,  product_name varchar2(100),  supplier_id number not null,  constraint fk_products_supplier      foreign key (supplier_id)      references tb_supplier(supplier_id)     on delete cascade);

驗證：1) 向表中插入測試資料

--主表插入範例資料insert into tb_supplier values (1,'microsoft','microsoft');insert into tb_supplier values (2,'ibm','ibm');insert into tb_supplier values (3,'linux','linux');insert into tb_supplier values (4,'dell','dell');insert into tb_supplier values (5,'lenovo','lenovo');insert into tb_supplier values (6,'google','google');--子表插入範例資料insert into tb_products values (1,'windows',1);insert into tb_products values (2,'office',1);insert into tb_products values (3,'informatica',2);insert into tb_products values (4,'congos',2);insert into tb_products values (5,'ubuntu',3);insert into tb_products values (6,'centos',3);insert into tb_products values (7,'inspiration',4);insert into tb_products values (8,'thinkpad',5);insert into tb_products values (9,'y410p',5);insert into tb_products values (10,'android',6);insert into tb_products values (11,'chrome',6);insert into tb_products values (12,'hadoop',6);--提交commit;

2) 刪除主表supplier_id=1的資料，並驗證子表中相關聯的資料是否被刪除

--刪除主表資料delete from tb_supplier where supplier_id=1;--提交commit;--驗證子表資料是否被刪除select * from tb_products;

3) 從上面的結果可以看出，相關聯的外鍵記錄已經被串聯刪除。

使用ALTER TABLE文法定義串聯刪除文法：

ALTER TABLE table_nameADD CONSTRAINT constraint_name      FOREIGN KEY (column1, column2,... column_n)      REFERENCES parent_table (column1, column2, ... column_n)      ON DELETE CASCADE;

樣本：1) 刪除之前的範例表

--刪除之前的範例表drop table tb_products;drop table tb_supplier;

2) 重建之前的範例表

--重建範例表create table tb_supplier(  supplier_id number not null,  supplier_name varchar2(50) not null,  contact_name varchar2(50),  CONSTRAINT pk_supplier PRIMARY KEY (supplier_id));create table tb_products(  product_id number not null,  product_name varchar2(100),  supplier_id number not null);

3) 為表增加外鍵

alter table tb_productsadd constraint fk_products_supplier    foreign key(supplier_id)    references tb_supplier(supplier_id)    on delete cascade;

4) 插入範例資料

--主表插入範例資料insert into tb_supplier values (1,'microsoft','microsoft');insert into tb_supplier values (2,'ibm','ibm');insert into tb_supplier values (3,'linux','linux');insert into tb_supplier values (4,'dell','dell');insert into tb_supplier values (5,'lenovo','lenovo');insert into tb_supplier values (6,'google','google');--子表插入範例資料insert into tb_products values (1,'windows',1);insert into tb_products values (2,'office',1);insert into tb_products values (3,'informatica',2);insert into tb_products values (4,'congos',2);insert into tb_products values (5,'ubuntu',3);insert into tb_products values (6,'centos',3);insert into tb_products values (7,'inspiration',4);insert into tb_products values (8,'thinkpad',5);insert into tb_products values (9,'y410p',5);insert into tb_products values (10,'android',6);insert into tb_products values (11,'chrome',6);insert into tb_products values (12,'hadoop',6);--提交commit;

5) 測試串聯刪除

--刪除主表資料delete from tb_supplier where supplier_id=1;--提交commit;

6) 驗證：

--驗證子表資料是否被刪除select * from tb_products;

7) 從上面的結果可以看出，相關聯的外鍵記錄已經被串聯刪除。

------------------------------------------------------------------------------------------------------------------------------------------------------

如果您們在嘗試的過程中遇到什麼問題或者My Code有錯誤的地方，請給予指正，非常感謝！

連絡方式：david.louis.tian@outlook.com

著作權＠：轉載請標明出處！
--------------------------------------------------------------------------------------------------------------------

ORACLE，在已有外鍵的情況下，怎通過SQL語句將外鍵連帶刪除約束ON DELTE CASCADE改為ON DELETE SET NULL

先把原來帶刪除級聯的外鍵刪掉，重新建立帶No Delete Set Null的外鍵。
例如：
alter table Emp drop constraint FK_Emp;
alter table Emp add constraint FK_Emp foreign key (DEPTNO)
references Dept (DEPTNO) on delete set null;
 
oracle資料庫中表格的串聯刪除問題

兩種方法，個人建議你選擇方法一，簡單方便

方法一：觸發器解決（下面的代碼可以不用修改，copy直接用）
create or replace trigger delete_dept
before delete on DEPT
for each row
begin
delete from EMP where DEPT_NO = :old.DEPT_NO;
delete from POS where DEPT_NO = :old.DEPT_NO;
end;
/

方法二：修改你的外鍵設定，達到串聯刪除的目的，具體實現如下：
a)先查詢出EMP表和POS表中 外鍵的名稱(如果你知道 外鍵名這一步可以省略)
select CONSTRAINT_NAME,TABLE_NAME from user_constraints where CONSTRAINT_TYPE ='R' and TABLE_NAME in('EMP','POS');

b)刪除EMP表和POS表上的外鍵後 重建立立允許串聯刪除的外鍵模式
alter table EMP drop constraint 外鍵名;
alter table POS drop constraint 外鍵名;
alter table EMP add constraint 外鍵名 foreign key(DEPT_NO) references DEPT(DEPT_NO) on delete cascade;
alter table POS add constraint 外鍵名 foreign key(DEPT_NO) references DEPT(DEPT_NO) on delete cascade;

---
以上，希望對你有所協助。