例如現有四張表為school
(學校)class
(課)、student
(學生)和enrollment
(註冊),彼此關聯如下。
一個學校有多堂課,所以學校school
和課class
是一對多關係(one to many),class.school_id
外鍵參考到school.id
。
一堂課可以有多個學生註冊、一位學生可以註冊多堂課,所以class
和student
是多對多關係(many to many),透過關聯表enrollment
來連結,其有兩外鍵:class_id
參考到class.id
;student_id
參考到student.id
。
使用一個SQL以school.id
來刪除子表class
、student
和enrollment
中對應的資料如下:
WITH del_enrollment AS (
DELETE FROM enrollment
USING class
WHERE class.id = enrollment.class_id
AND class.school_id = :school_id
RETURNING *
)
DELETE FROM student
USING del_enrollment, class
WHERE del_enrollment.class_id = class.id
AND del_enrollment.student_id = student.id
AND class.school_id = :school_id;
DELETE USING
相當於DELETE JION
。
因為在未設定DELETE CASCADE
的情況下,若先刪除enrollment
的資料,則丟失了enrollment.student_id
,也就無法依此刪除student
中的對應資料。所以先透過WITH
將內部DELETE
刪除的資料暫存到del_enrollment
來作為後面刪除student
資料時的參考。
沒有留言:
張貼留言