Untuk mencegah ID artikel muncul lebih dari sekali di salah satu dari dua kolom referensi, masukkan kedua peran ke dalam tabel referensi dengan satu ID artikel unik. Jika setiap baris hanya boleh memiliki tepat satu kolom terisi, indeks ekspresi unik merupakan pilihan yang lebih sederhana.

Tutorial ini ditujukan bagi pengembang yang bekerja di macOS Terminal dengan PostgreSQL dan node-postgres. Tugasnya adalah menerapkan satu kumpulan bersama untuk referensi artikel: setelah sebuah artikel direferensikan dalam salah satu peran, referensi lain ke artikel itu harus gagal. Semua baris contoh bersifat sintetis.

Apa arti keunikan pada salah satu kolom?

Anggap kedua kolom merujuk ke katalog artikel yang sama. Aturan yang diinginkan adalah setiap ID artikel yang tidak null muncul paling banyak satu kali di semua posisi referensi, termasuk dua posisi dalam satu baris. Aturan ini lebih ketat daripada menjadikan setiap kolom unik secara terpisah.

Bundel Artikel utama Artikel sekunder Hasil menurut aturan yang diinginkan
1 101 102 Terima
2 103 101 Tolak: 101 sudah muncul di bundel 1
3 104 104 Tolak: artikel yang sama menempati kedua posisi

UNIQUE (primary_article_id, secondary_article_id) komposit menjadikan kombinasinya unik. Batasan unik yang terpisah menjadikan setiap kolom unik secara independen. Kedua rancangan tersebut tidak menolak pengulangan lintas kolom antara bundel 1 dan 2. PostgreSQL mendokumentasikan bahwa indeks unik multikolom menolak duplikat ketika semua nilai yang diindeks sama. Indeks Unik

Pilih rancangan berdasarkan bentuk baris yang diizinkan sebelum menulis kueri insert:

Bentuk baris yang diwajibkan Rancangan yang sesuai
Kedua referensi boleh terisi Simpan referensi sebagai baris; jadikan article_id unik
Tepat satu referensi harus terisi Terapkan aturan tepat satu dengan pemeriksaan baris; indekskan COALESCE secara unik
Nol atau satu referensi boleh terisi Gunakan pemeriksaan maksimal satu dan indeks ekspresi yang sama
Kolom merujuk ke katalog yang berbeda Tentukan terlebih dahulu apakah ID numerik yang sama mewakili hal yang sama; tutorial ini mengasumsikan satu katalog bersama

Bagaimana menerapkan aturan jika kedua referensi diizinkan?

Representasikan setiap referensi sebagai baris yang berisi bundel, peran, dan ID artikel. Peran mempertahankan makna kolom asli. Satu batasan unik pada article_id kemudian mencakup kedua peran.

Skema di bawah menggabungkan batasan PostgreSQL biasa: kunci asing mewajibkan baris katalog yang cocok, kunci primer komposit mengizinkan satu referensi per bundel dan slot, dan batasan unik bernama mengizinkan setiap artikel hanya satu kali. Mekanisme batasan ini didokumentasikan dalam Batasan.

Buat contoh yang dapat direproduksi

Gunakan basis data contoh lokal yang kosong dengan PostgreSQL 14–18 dan perintah psql yang tersedia. Simpan seluruh penyiapan berikut sebagai schema.sql. Contoh ini menggunakan tabel biasa yang tidak dipartisi dan tidak memerlukan ekstensi.

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;

Di macOS Terminal, atur PGDATABASE ke nama basis data contoh yang sudah ada, lalu jalankan berkas tersebut. Perintah ini mengasumsikan pengaturan koneksi lokal Anda sudah berfungsi.

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

Rancangan ini mengizinkan sebuah bundel memiliki nol, satu, atau dua referensi. Rancangan ini menerapkan keunikan dan batas maksimum dua peran; rancangan ini tidak mewajibkan setiap bundel memiliki kedua peran. Itu merupakan persyaratan terpisah. Referensi yang tidak ada direpresentasikan dengan tidak adanya baris.

Pertahankan bentuk dua kolom untuk pembacaan

Aplikasi tetap dapat mengambil satu baris per bundel dengan dua kolom referensi. Jalankan kueri ini setelah penyiapan:

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 awalnya adalah bundel 1 dengan referensi 101 dan 102. Kunci primer skema memastikan setiap join menemukan paling banyak satu referensi untuk slotnya. Gunakan kueri ini sebagai bentuk pembacaan aplikasi, alih-alih menyimpan salinan kedua dari nilai referensi.

Bagaimana node-postgres sebaiknya memasukkan kedua referensi?

Buat bundel dan referensinya dalam satu transaksi. Jika ada referensi yang berbenturan, batalkan transaksi bundel itu juga. node-postgres mewajibkan setiap pernyataan dalam transaksi menggunakan klien yang sama dan sudah dipinjam; pemanggilan pool.query() secara terpisah tidak cocok untuk urutan ini. Transaksi

Di direktori proyek lokal, instal paket pg yang didokumentasikan. Gunakan rilis LTS Node.js terkini dan simpan program di bawah sebagai demo.mjs. Jalankan setelah penyiapan SQL, dengan lingkungan koneksi yang sama. node-postgres mendokumentasikan instalasi dan koneksi berbasis lingkungan.

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 meneruskan nilai melalui parameter bernomor dan larik nilai terpisah, mengikuti API Kueri node-postgres. Keluaran yang diharapkan saat pertama kali dijalankan adalah:

Bundel 2 ditambahkan
Bundel 3 ditolak: artikel sudah direferensikan

Transaksi kedua mencoba menggunakan kembali artikel 101. Rollback-nya menghapus bundel 3 dan membiarkan artikel 105 tanpa referensi. Hanya konflik keunikan dengan nama khusus yang menampilkan pesan duplikat; kegagalan lain diteruskan. PostgreSQL mengidentifikasi pelanggaran keunikan dengan SQLSTATE 23505 dan menyediakan nama batasan secara terpisah dari teks pesan. Kode Galat PostgreSQL

Jalankan kembali kueri pembacaan sebelumnya untuk memeriksa hasilnya: bundel 1 dan 2 seharusnya tetap ada, dengan referensi 101–104. Anggap ini sebagai demonstrasi yang dijalankan sekali untuk setiap penyiapan baru: jika program dijalankan ulang, program akan menemui kunci primer bundel 2 yang sudah ada.

Kapan indeks unik pada COALESCE cukup?

Gunakan rancangan yang lebih kecil ini hanya jika sebuah baris memuat paling banyak satu referensi yang sebenarnya. COALESCE mengembalikan argumen non-null pertamanya, sehingga kolom pertama yang terisi menyembunyikan kolom kedua dari ekspresi yang diindeks. PostgreSQL juga mensyaratkan argumennya memiliki tipe yang sama. Ekspresi Kondisional

Sebagai contoh, baris (101, 102) dan (103, 101) menghasilkan nilai ekspresi 101 dan 103. Indeks unik pada nilai ekspresi tersebut menerima keduanya, meskipun artikel 101 muncul dua kali. Contoh tandingan ini menjelaskan mengapa ekspresi tersebut memerlukan batasan bentuk baris.

Untuk aturan tepat satu, jalankan contoh alternatif berikut setelah membuat katalog articles sebelumnya. Tabel terpisahnya menunjukkan skema alternatif, alih-alih menambahkan referensi ke 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;

Pemeriksaan ini membandingkan dua hasil Boolean non-null dan hanya menerima ketika satu kolom null dan kolom lainnya terisi. PostgreSQL mendukung pemeriksaan yang melibatkan beberapa kolom dalam baris yang sama. Batasan PostgreSQL 14

Indeks ekspresi unik memasukkan nilai terisi dari kolom mana pun ke dalam satu domain pembandingan. Indeks ekspresi didokumentasikan di PostgreSQL terkini dan PostgreSQL 14; pendekatan ini tidak memerlukan sintaks penanganan null tambahan dari PostgreSQL 15.

Insert kedua tidak menghasilkan baris karena artikel 101 sudah direferensikan. Ekspresi dapat menjadi target ON CONFLICT, dan RETURNING melaporkan baris yang benar-benar disisipkan atau diperbarui. Anggap hasil kosong sebagai insert yang dilewati. Nama indeks ekspresi bukan nama batasan tabel untuk ON CONFLICT ON CONSTRAINT. INSERT

Jika kedua kolom boleh kosong, ganti pemeriksaan tepat satu dengan kondisi tingkat baris berikut saat mendefinisikan tabel. Pertahankan indeks ekspresinya. Beberapa baris yang semuanya null kemudian diizinkan berdasarkan perilaku null indeks unik bawaan PostgreSQL. Indeks Unik

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

Bagaimana mendiagnosis duplikat sebelum mengubah tabel yang sudah ada?

Perluas kedua kolom menjadi satu rangkaian dengan UNION ALL, lalu kelompokkan berdasarkan ID artikel. Mempertahankan duplikat sangat penting: UNION biasa dapat menghapus bukti pengulangan yang perlu Anda temukan. Diagnostik lengkap ini menggunakan masukan sintetis dan berjalan secara independen dari 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 duplikat yang diharapkan adalah 101 dan 104, masing-masing dengan dua kemunculan. Untuk tabel yang sudah ada, ganti CTE pertama dengan kolom aktual tabel tersebut. Tinjau setiap kemunculan yang dilaporkan sebelum migrasi; menghapus baris secara otomatis dapat membuang referensi yang memang dimaksudkan. Pindahkan semua referensi ke representasi yang dipilih dan arahkan penulisan berikutnya ke sana, karena tabel alternatif yang terpisah dalam contoh tidak berbagi penerapan keunikan.

Mengapa basis data harus menerapkan keputusan akhir?

Pemeriksaan awal dapat menjelaskan konflik yang sudah ada, tetapi tidak dapat menjamin keunikan dengan sendirinya. Dua operasi serentak dapat sama-sama melihat referensi yang belum digunakan sebelum salah satunya memasukkan data. Implementasi keunikan PostgreSQL memeriksa konflik sebagai bagian dari penyisipan indeks, menunggu transaksi yang belum dikomit dan berkonflik bila perlu, lalu memeriksa ulang hasilnya. Pemeriksaan Keunikan Indeks

Jangan mengganti mekanisme itu dengan CHECK yang memeriksa baris lain. PostgreSQL secara eksplisit tidak mendukung pemeriksaan lintas baris sebagai jaminan yang terus dipertahankan. Gunakan pemeriksaan baris untuk bentuk kolom yang diizinkan serta batasan atau indeks unik untuk referensi yang berulang. Batasan

Program JavaScript lolos pemeriksaan sintaks, dan contoh tandingan serta tabel kebenaran aturan tepat satu diperiksa secara deterministik. Contoh PostgreSQL dicocokkan dengan dokumentasi, tetapi tidak dijalankan terhadap basis data dalam lingkungan riset ini.

Untuk dua posisi referensi yang terisi, rancangan tabel referensi menerapkan aturan secara langsung melalui satu ID artikel unik. Untuk pilihan eksklusif antara kolom-kolom, rancangan COALESCE yang dilengkapi pemeriksaan mempertahankan skema yang lebih kecil. Pilih berdasarkan bentuk baris yang diizinkan, lalu biarkan basis data mengambil keputusan akhir tentang keunikan.

Sumber