AdSense

網頁

2024/7/16

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

例如現有四張表為school(學校)class(課)、student(學生)和enrollment(註冊),彼此關聯如下。


一個學校有多堂課,所以學校school和課class是一對多關係(one to many),class.school_id外鍵參考到school.id

一堂課可以有多個學生註冊、一位學生可以註冊多堂課,所以classstudent是多對多關係(many to many),透過關聯表enrollment來連結,其有兩外鍵:class_id參考到class.idstudent_id參考到student.id



使用一個SQL以school.id來刪除子表classstudentenrollment中對應的資料如下:

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資料時的參考。

沒有留言:

AdSense