PostgreSQL 以主表ID同時刪除有外鍵依賴的子表和關聯表資料

例如現有四張表為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資料時的參考。