Bagaimana untuk Melaksanakan Pemadaman Induk Bertingkat dengan Kekangan Utama Asing dalam PostgreSQL?

Meratakan Pemadaman Induk dengan Kekangan Kunci Asing

Dalam sistem pangkalan data hubungan, memadamkan baris anak selalunya memerlukan pemadaman induk jika ia tidak lagi dirujuk oleh mana-mana kanak-kanak lain. Tugas ini boleh dicapai dalam PostgreSQL versi 9.1 dan kemudian menggunakan CTE pengubah suai data (Ungkapan Jadual Biasa).

Pendekatan CTE Pengubahsuaian Data

<code class="sql">WITH del_child AS (
    DELETE FROM child
    WHERE  child_id = 1
    RETURNING parent_id, child_id
    )
DELETE FROM parent p
USING  del_child x
WHERE  p.parent_id = x.parent_id
AND    NOT EXISTS (
   SELECT FROM child c
   WHERE  c.parent_id = x.parent_id
   AND    c.child_id <> x.child_id   -- !
   );</code>

Ini CTE mula-mula memadamkan baris anak yang ditentukan. Ia kemudian mengembalikan ID induk dan ID anak yang dipadamkan. Pernyataan DELETE kedua menggunakan maklumat ini untuk memadamkan baris induk jika ia tidak mempunyai anak lain yang tinggal. Syaratnya c.child_id <> x.child_id memastikan bahawa hanya kanak-kanak selain daripada yang dipadamkan dipertimbangkan.

Menghapuskan Syarat Perlumbaan

Untuk mengelakkan keadaan perlumbaan yang berpotensi di mana transaksi serentak boleh membawa kepada keputusan yang tidak dijangka , baris induk boleh dikunci sebelum proses pemadaman. Ini dicapai dengan menggunakan klausa FOR NO KEY UPDATE dalam CTE untuk mengunci baris induk.

<code class="sql">WITH lock_parent AS (
   SELECT p.parent_id, c.child_id
   FROM   child  c
   JOIN   parent p ON p.parent_id = c.parent_id
   WHERE  c.child_id = 12              -- provide child_id here once
   FOR    NO KEY UPDATE                -- locks parent row.
   )
 , del_child AS (
   DELETE FROM child c
   USING  lock_parent l
   WHERE  c.child_id = l.child_id
   )
DELETE FROM parent p
USING  lock_parent l
WHERE  p.parent_id = l.parent_id
AND    NOT EXISTS (
   SELECT FROM child c
   WHERE  c.parent_id = l.parent_id
   AND    c.child_id <> l.child_id   -- !
   );</code>

Pendekatan ini mengehadkan pelaksanaan operasi DELETE berisiko kepada satu transaksi, dengan berkesan menghapuskan kemungkinan berlaku serentak gangguan.

Atas ialah kandungan terperinci Bagaimana untuk Melaksanakan Pemadaman Induk Bertingkat dengan Kekangan Utama Asing dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!