Untuk menghalang ID artikel daripada muncul lebih daripada sekali dalam mana-mana satu daripada dua lajur rujukan, letakkan kedua-dua peranan dalam jadual rujukan dengan satu ID artikel unik. Jika setiap baris membenarkan tepat satu lajur diisi, indeks ungkapan unik ialah pilihan yang lebih ringkas.

Tutorial ini ditujukan kepada pembangun yang menggunakan PostgreSQL dan node-postgres dalam Terminal macOS. Tugasnya ialah menguatkuasakan satu kumpulan rujukan artikel yang dikongsi: sebaik sahaja artikel dirujuk dalam mana-mana satu peranan, rujukan lain kepada artikel itu mesti gagal. Semua baris sampel ialah data sintetik.

Apakah maksud keunikan merentas mana-mana lajur?

Andaikan kedua-dua lajur merujuk kepada katalog artikel yang sama. Peraturan yang dimaksudkan ialah setiap ID artikel bukan nol muncul paling banyak sekali dalam semua kedudukan rujukan, termasuk dua kedudukan dalam satu baris. Ini lebih ketat daripada menjadikan setiap lajur unik secara berasingan.

Himpunan Artikel utama Artikel sekunder Hasil mengikut peraturan yang dimaksudkan
1 101 102 Terima
2 103 101 Tolak: 101 sudah muncul dalam himpunan 1
3 104 104 Tolak: artikel yang sama menduduki kedua-dua kedudukan

UNIQUE (primary_article_id, secondary_article_id) komposit menjadikan gabungan itu unik. Kekangan unik yang berasingan menjadikan setiap lajur unik secara tersendiri. Kedua-dua reka bentuk itu tidak menolak pengulangan merentas lajur antara himpunan 1 dan 2. PostgreSQL mendokumenkan bahawa indeks unik berbilang lajur menolak pendua apabila semua nilai yang diindeks sepadan. Indeks Unik

Pilih reka bentuk berdasarkan bentuk baris yang dibenarkan sebelum menulis pertanyaan sisipan:

Bentuk baris yang diperlukan Reka bentuk yang sesuai
Kedua-dua rujukan boleh diisi Simpan rujukan sebagai baris; jadikan article_id unik
Tepat satu rujukan mesti diisi Kuatkuasakan tepat satu dengan semakan baris; indekskan COALESCE secara unik
Sifar atau satu rujukan boleh diisi Gunakan semakan paling banyak satu dan indeks ungkapan yang sama
Lajur merujuk kepada katalog yang berbeza Tentukan dahulu sama ada ID angka yang sama mewakili perkara yang sama; tutorial ini mengandaikan satu katalog yang dikongsi

Bagaimanakah anda menguatkuasakan peraturan apabila kedua-dua rujukan dibenarkan?

Wakilkan setiap rujukan sebagai baris yang mengandungi himpunan, peranan dan ID artikel. Peranan mengekalkan maksud lajur asal. Satu kekangan unik pada article_id kemudian meliputi kedua-dua peranan.

Skema di bawah menggabungkan kekangan PostgreSQL biasa: kunci asing memerlukan baris katalog yang sepadan, kunci utama komposit membenarkan satu rujukan bagi setiap himpunan dan slot, dan kekangan unik bernama membenarkan setiap artikel sekali sahaja. Mekanisme kekangan ini didokumenkan dalam Kekangan.

Cipta sampel yang boleh dihasilkan semula

Gunakan pangkalan data contoh tempatan yang kosong dengan PostgreSQL 14–18 dan arahan psql yang tersedia. Simpan persediaan lengkap berikut sebagai schema.sql. Ia menggunakan jadual biasa yang tidak dipartisi dan tidak memerlukan sambungan.

BEGIN;

CREATE TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TABLE article_bundles (
    bundle_id integer PRIMARY KEY,
    label text NOT NULL
);

CREATE TABLE bundle_refs (
    bundle_id integer NOT NULL
        REFERENCES article_bundles (bundle_id),
    slot text NOT NULL
        CHECK (slot IN ('primary', 'secondary')),
    article_id integer NOT NULL
        REFERENCES articles (article_id),
    PRIMARY KEY (bundle_id, slot),
    CONSTRAINT bundle_refs_article_once UNIQUE (article_id)
);

INSERT INTO articles (article_id, title) VALUES
    (101, 'Grid spacing'),
    (102, 'Type scale'),
    (103, 'Page margins'),
    (104, 'Caption layout'),
    (105, 'Colour palette');

INSERT INTO article_bundles (bundle_id, label)
VALUES (1, 'Opening notes');

INSERT INTO bundle_refs (bundle_id, slot, article_id) VALUES
    (1, 'primary', 101),
    (1, 'secondary', 102);

COMMIT;

Dalam Terminal macOS, tetapkan PGDATABASE kepada nama pangkalan data contoh sedia ada itu, kemudian jalankan fail tersebut. Arahan ini mengandaikan tetapan sambungan tempatan anda sudah berfungsi.

export PGDATABASE=article_demo
psql -v ON_ERROR_STOP=1 -f schema.sql

Reka bentuk ini membenarkan himpunan mempunyai sifar, satu atau dua rujukan. Ia menguatkuasakan keunikan dan had maksimum dua peranan; ia tidak mewajibkan setiap himpunan mempunyai kedua-dua peranan. Itu keperluan yang berasingan. Rujukan yang tiada diwakili dengan ketiadaan baris.

Kekalkan bentuk dua lajur untuk bacaan

Aplikasi masih boleh mendapatkan satu baris bagi setiap himpunan dengan dua medan rujukan. Jalankan pertanyaan ini selepas persediaan:

SELECT b.bundle_id,
       b.label,
       p.article_id AS primary_article_id,
       s.article_id AS secondary_article_id
FROM article_bundles AS b
LEFT JOIN bundle_refs AS p
  ON p.bundle_id = b.bundle_id AND p.slot = 'primary'
LEFT JOIN bundle_refs AS s
  ON s.bundle_id = b.bundle_id AND s.slot = 'secondary'
ORDER BY b.bundle_id;

Hasil awal ialah himpunan 1 dengan rujukan 101 dan 102. Kunci utama skema memastikan setiap cantuman menemukan paling banyak satu rujukan bagi slotnya. Gunakan pertanyaan ini sebagai bentuk bacaan aplikasi dan bukannya menyelenggara salinan kedua nilai rujukan.

Bagaimanakah node-postgres patut menyisipkan kedua-dua rujukan?

Cipta himpunan dan rujukannya dalam satu transaksi. Jika mana-mana rujukan bercanggah, undurkan himpunan itu juga. node-postgres memerlukan setiap pernyataan dalam transaksi menggunakan klien yang sama yang telah diperoleh; panggilan pool.query() yang berasingan tidak sesuai untuk urutan ini. Transaksi

Dalam direktori projek tempatan, pasang pakej pg yang didokumenkan. Gunakan keluaran LTS Node.js semasa dan simpan program di bawah sebagai demo.mjs. Jalankannya selepas persediaan SQL, dengan persekitaran sambungan yang sama. node-postgres mendokumenkan pemasangan dan sambungan berasaskan persekitaran.

npm install pg
node demo.mjs

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool();

async function addBundle(bundleId, label, primaryId, secondaryId) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      'INSERT INTO article_bundles (bundle_id, label) VALUES ($1, $2)',
      [bundleId, label]
    );

    await client.query(
      `INSERT INTO bundle_refs (bundle_id, slot, article_id)
       VALUES ($1, 'primary', $2), ($1, 'secondary', $3)`,
      [bundleId, primaryId, secondaryId]
    );

    await client.query('COMMIT');
    console.log(`Added bundle ${bundleId}`);
  } catch (error) {
    await client.query('ROLLBACK');

    if (
      error.code === '23505' &&
      error.constraint === 'bundle_refs_article_once'
    ) {
      console.log(
        `Bundle ${bundleId} rejected: an article is already referenced`
      );
      return;
    }

    throw error;
  } finally {
    client.release();
  }
}

try {
  await addBundle(2, 'Layout notes', 103, 104);
  await addBundle(3, 'Colour notes', 105, 101);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

Program ini menghantar nilai melalui parameter bernombor dan tatasusunan nilai yang berasingan, mengikut API node-postgres Pertanyaan. Output yang dijangka pada larian pertama ialah:

Added bundle 2
Bundle 3 rejected: an article is already referenced

Transaksi kedua cuba menggunakan semula artikel 101. Undurannya membuang himpunan 3 dan membiarkan artikel 105 tanpa rujukan. Hanya konflik keunikan yang dinamakan secara khusus menerima mesej pendua; kegagalan lain akan diteruskan. PostgreSQL mengenal pasti pelanggaran keunikan dengan SQLSTATE 23505 dan membekalkan nama kekangan secara berasingan daripada teks mesej. Kod Ralat PostgreSQL

Jalankan semula pertanyaan bacaan tadi untuk memeriksa hasilnya: himpunan 1 dan 2 sepatutnya kekal dengan rujukan 101–104. Anggap ini sebagai demonstrasi sekali bagi persediaan baharu; jika program dijalankan semula, kunci utama himpunan 2 yang sedia ada akan menyebabkan ralat.

Bilakah indeks unik pada COALESCE mencukupi?

Gunakan reka bentuk yang lebih kecil ini hanya apabila satu baris mengandungi paling banyak satu rujukan sebenar. COALESCE memulangkan argumen bukan nol yang pertama, jadi lajur pertama yang diisi menyembunyikan lajur kedua daripada ungkapan yang diindeks. PostgreSQL juga memerlukan argumennya mempunyai jenis sepunya. Ungkapan Bersyarat

Sebagai contoh, baris (101, 102) dan (103, 101) menghasilkan nilai ungkapan 101 dan 103. Indeks unik pada nilai ungkapan tersebut menerima kedua-duanya, walaupun artikel 101 muncul dua kali. Contoh balas ini menjelaskan sebab ungkapan tersebut memerlukan kekangan bentuk baris.

Untuk peraturan tepat satu, jalankan contoh alternatif berikut selepas mencipta katalog articles terdahulu. Jadual berasingannya menunjukkan skema alternatif, bukannya menambah rujukan kepada bundle_refs.

CREATE TABLE single_article_refs (
    reference_id integer PRIMARY KEY,
    primary_article_id integer REFERENCES articles (article_id),
    secondary_article_id integer REFERENCES articles (article_id),
    CONSTRAINT exactly_one_article CHECK (
        (primary_article_id IS NULL)
        <> (secondary_article_id IS NULL)
    )
);

CREATE UNIQUE INDEX single_article_refs_article_once
ON single_article_refs (
    (COALESCE(primary_article_id, secondary_article_id))
);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (1, 101, NULL);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (2, NULL, 101)
ON CONFLICT (
    (COALESCE(primary_article_id, secondary_article_id))
)
DO NOTHING
RETURNING reference_id;

Semakan ini membandingkan dua hasil Boolean bukan nol dan hanya menerima apabila satu lajur bernilai nol manakala lajur yang satu lagi diisi. PostgreSQL menyokong semakan yang melibatkan berbilang lajur dalam baris yang sama. Kekangan PostgreSQL 14

Indeks ungkapan unik meletakkan nilai terisi daripada mana-mana lajur ke dalam satu domain perbandingan. Indeks ungkapan didokumenkan dalam PostgreSQL semasa dan PostgreSQL 14; pendekatan ini tidak memerlukan sintaks pengendalian nol tambahan dalam PostgreSQL 15.

Sisipan kedua tidak memulangkan baris kerana artikel 101 sudah dirujuk. Ungkapan boleh menjadi sasaran ON CONFLICT, dan RETURNING melaporkan baris yang benar-benar disisipkan atau dikemas kini. Anggap hasil kosong sebagai sisipan yang dilangkau. Nama indeks ungkapan itu bukan nama kekangan jadual untuk ON CONFLICT ON CONSTRAINT. INSERT

Jika kedua-dua lajur boleh kosong, gantikan semakan tepat satu dengan syarat peringkat baris berikut semasa mentakrifkan jadual. Kekalkan indeks ungkapan itu. Berbilang baris yang kesemuanya bernilai nol dibenarkan dengan tingkah laku nol lalai indeks unik PostgreSQL. Indeks Unik

-- Alternative CHECK fragment for the CREATE TABLE definition:
CHECK (
    primary_article_id IS NULL
    OR secondary_article_id IS NULL
)

Bagaimanakah anda mendiagnosis pendua sebelum mengubah jadual sedia ada?

Kembangkan kedua-dua lajur menjadi satu aliran dengan UNION ALL, kemudian kumpulkan mengikut ID artikel. Penting untuk mengekalkan pendua: UNION biasa mungkin menyingkirkan bukti pengulangan yang perlu anda temukan. Diagnostik lengkap ini menggunakan input sintetik dan berjalan secara berasingan daripada skema di atas.

WITH legacy_refs (bundle_id, primary_article_id, secondary_article_id) AS (
    VALUES
        (1, 101, 102),
        (2, 103, 101),
        (3, 104, 104)
),
expanded AS (
    SELECT bundle_id, 'primary' AS slot,
           primary_article_id AS article_id
    FROM legacy_refs
    WHERE primary_article_id IS NOT NULL

    UNION ALL

    SELECT bundle_id, 'secondary' AS slot,
           secondary_article_id AS article_id
    FROM legacy_refs
    WHERE secondary_article_id IS NOT NULL
)
SELECT article_id,
       count(*) AS occurrences,
       string_agg(
           bundle_id::text || ':' || slot,
           ', ' ORDER BY bundle_id, slot
       ) AS locations
FROM expanded
GROUP BY article_id
HAVING count(*) > 1
ORDER BY article_id;

ID pendua yang dijangka ialah 101 dan 104, masing-masing dengan dua kejadian. Untuk jadual sedia ada, gantikan CTE pertama dengan lajur sebenar jadual tersebut. Semak setiap kejadian yang dilaporkan sebelum migrasi; membuang baris secara automatik boleh menghapuskan rujukan yang disengajakan. Pindahkan semua rujukan ke perwakilan yang dipilih dan halakan penulisan seterusnya ke sana, kerana jadual alternatif berasingan dalam sampel tidak berkongsi penguatkuasaan keunikan.

Mengapakah pangkalan data mesti menguatkuasakan keputusan muktamad?

Carian awal boleh menerangkan konflik sedia ada, tetapi dengan sendirinya tidak dapat menjamin keunikan. Dua operasi serentak boleh sama-sama melihat rujukan yang belum digunakan sebelum salah satu daripadanya membuat sisipan. Pelaksanaan keunikan PostgreSQL memeriksa konflik semasa penyisipan indeks, menunggu transaksi bercanggah yang belum dimuktamadkan apabila perlu, kemudian menyemak semula hasilnya. Semakan Keunikan Indeks

Jangan gantikan mekanisme itu dengan CHECK yang membuat pertanyaan terhadap baris lain. PostgreSQL secara jelas tidak menyokong semakan merentas baris sebagai jaminan yang diselenggara secara berterusan. Gunakan semakan baris untuk bentuk lajur yang dibenarkan dan kekangan atau indeks unik untuk rujukan berulang. Kekangan

Program JavaScript lulus semakan sintaks, dan contoh balas serta jadual kebenaran tepat satu telah diperiksa secara deterministik. Contoh PostgreSQL telah disemak berdasarkan dokumentasi tetapi tidak dilaksanakan terhadap pangkalan data dalam persekitaran penyelidikan ini.

Untuk dua kedudukan rujukan yang diisi, reka bentuk jadual rujukan menjadikan peraturan itu boleh dikuatkuasakan secara langsung melalui satu ID artikel unik. Untuk pilihan eksklusif antara lajur, reka bentuk COALESCE yang disemak mengekalkan skema yang lebih kecil. Pilih berdasarkan bentuk baris yang dibenarkan, kemudian biarkan pangkalan data membuat keputusan muktamad tentang keunikan.

Sumber