Foreign key komposit PostgreSQL memerlukan kunci unik yang memenuhi syarat dan mencakup kolom yang dirujuk. Kunci utama pada id saja tidak memenuhi syarat untuk referensi ke (id, edition), meskipun secara logis membuat pasangan tersebut unik. PostgreSQL: CREATE TABLE

Panduan ini membantu developer yang bekerja di Terminal pada Mac mendiagnosis ketidakcocokan tersebut, memilih relasi yang tepat, dan menguji hasilnya dengan node-postgres. Semua baris contoh menjelaskan artikel sintetis. SQL ini menargetkan PostgreSQL 18 dan foreign key biasa yang menggunakan kesetaraan; PostgreSQL 18 mendokumentasikan aturan terpisah untuk referensi temporal yang menggunakan PERIOD. PostgreSQL: CREATE TABLE

Mengapa PostgreSQL menolak referensi komposit tersebut?

Misalkan sebuah tabel artikel memiliki tiga kolom: id, edition, dan title. Kunci utamanya adalah id. Tabel lain menyimpan referensi artikel sebagai dua nilai: article_id dan edition.

Relasi yang dimaksud dinyatakan dengan fragmen batasan tabel berikut:

FOREIGN KEY (article_id, edition)
  REFERENCES demo_articles (id, edition)

Deklarasi itu meminta PostgreSQL memeriksa kedua nilai secara bersamaan terhadap satu baris induk. Target yang didokumentasikan harus berupa kolom-kolom dalam batasan unik atau kunci utama yang tidak dapat ditangguhkan, atau kolom-kolom dalam indeks unik nonparsial. Kunci utama yang ada hanya mencakup id; referensi tersebut menyebut (id, edition). PostgreSQL: CREATE TABLE

Alasan matematis di balik keberatan ini dapat dipahami: jika tidak ada dua baris induk yang dapat memiliki id yang sama, keduanya juga tidak dapat memiliki pasangan (id, edition) yang sama. Meski demikian, implikasi itu tidak menyediakan target yang dideklarasikan dan sesuai, sebagaimana disyaratkan oleh definisi foreign key. Untuk skema ini, menambahkan UNIQUE (id, edition) menyediakannya. Batasan tambahan ini berlebihan secara logis untuk keunikan, tetapi memiliki fungsi nyata sebagai target referensi komposit.

Perhatikan baris induk ilustratif berikut:

id edition title
101 morning Paper Shapes
102 evening Color Notes

Pasangan anak (101, 'morning') memiliki kecocokan. Pasangan (101, 'evening') tidak: kedua nilainya muncul di tabel, tetapi pada baris yang berbeda. Inilah alasan menggunakan relasi komposit saat kedua nilai yang disimpan harus menjelaskan artikel yang sama.

Sebaiknya Anda menambahkan kunci unik atau menyederhanakan foreign key?

Tentukan relasinya sebelum mengubah skema. Pertanyaan yang tepat adalah apakah tabel anak perlu menyimpan dan menegakkan edition bersama pengenal artikel.

Perilaku yang diperlukan Desain yang sesuai Konsekuensi
Tabel anak mengidentifikasi artikel dan menampilkan edition terkini Simpan article_id; rujuk id induk Ambil edition dari tabel induk saat melakukan kueri
Tabel anak menyimpan edition yang harus sesuai dengan artikelnya Pertahankan referensi komposit; tambahkan UNIQUE (id, edition) ke tabel induk PostgreSQL memeriksa bahwa pasangan yang disimpan muncul secara bersamaan
Nomor artikel yang sama dapat mengidentifikasi beberapa baris edition Modelkan identitas induk sebagai (id, edition) Kunci utama yang hanya berisi id akan mencegah baris induk tersebut

Baris terakhir menggambarkan model data yang berbeda. Jangan hapus kunci utama satu kolom hanya untuk menghilangkan pesan kesalahan: tentukan terlebih dahulu apakah nomor artikel yang berulang memang seharusnya ada di tabel induk. PostgreSQL mengizinkan kunci utama komposit, dan sebuah tabel hanya dapat memiliki satu kunci utama. PostgreSQL: Constraints

Untuk contoh yang dapat dijalankan di bawah ini, pengenal artikel tetap unik secara global. Tabel anak sengaja menyimpan edition juga, sehingga tabel induk mempertahankan PRIMARY KEY (id) dan memperoleh pasangan unik tambahan.

Bagaimana cara mereproduksi dan memperbaiki kesalahan di Mac?

Gunakan basis data PostgreSQL 18 lokal yang sudah tersedia dan instalasi Node.js yang berfungsi; panduan ini mengasumsikan keduanya sudah tersedia. Buka Terminal dan buat direktori proyek baru. Instal driver dengan perintah paket yang didokumentasikan. node-postgres: Welcome

mkdir composite-fk-demo
cd composite-fk-demo
npm init -y
npm install pg

Simpan program lengkap berikut sebagai demo.mjs. Program ini membuat tabel sementara, menunjukkan skema yang ditolak, menambahkan kunci yang sesuai, lalu menguji pasangan artikel yang valid dan tidak valid. Semua pernyataan menggunakan satu klien karena node-postgres mengharuskan transaksi tetap menggunakan instans klien yang sama. node-postgres: Transactions

Helper ini menggunakan savepoint di sekitar setiap kegagalan yang diharapkan agar transaksi dapat dilanjutkan. PostgreSQL mendokumentasikan ROLLBACK TO SAVEPOINT sebagai perintah untuk membatalkan perintah setelah savepoint tersebut dan memungkinkan pekerjaan dilanjutkan. PostgreSQL: ROLLBACK TO SAVEPOINT

import pg from 'pg';
const { Client } = pg;
const client = new Client();

const childDDL = `CREATE TEMP TABLE demo_article_links (
  link_id integer PRIMARY KEY,
  article_id integer NOT NULL,
  edition text NOT NULL,
  FOREIGN KEY (article_id, edition)
    REFERENCES demo_articles (id, edition)
)`;

async function expectError(sql, values, code) {
  await client.query('SAVEPOINT expected_error');
  try {
    await client.query(sql, values);
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT expected_error');
    await client.query('RELEASE SAVEPOINT expected_error');
    if (error.code !== code) throw error;
    console.log(`Expected error: ${code}`);
    return;
  }
  await client.query('ROLLBACK TO SAVEPOINT expected_error');
  await client.query('RELEASE SAVEPOINT expected_error');
  throw new Error(`Expected SQLSTATE ${code}, but query succeeded`);
}

try {
  await client.connect();
  await client.query('BEGIN');
  await client.query(`CREATE TEMP TABLE demo_articles (
    id integer PRIMARY KEY,
    edition text NOT NULL,
    title text NOT NULL
  )`);

  await expectError(childDDL, [], '42830');

  await client.query(`ALTER TABLE demo_articles
    ADD CONSTRAINT demo_articles_id_edition_key
    UNIQUE (id, edition)`);
  await client.query(childDDL);

  await client.query(`INSERT INTO demo_articles (id, edition, title)
    VALUES ($1, $2, $3), ($4, $5, $6)`,
    [101, 'morning', 'Paper Shapes', 102, 'evening', 'Color Notes']);

  await client.query(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [1, 101, 'morning']);

  await expectError(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [2, 101, 'evening'], '23503');

  const result = await client.query(`SELECT l.link_id, a.title, a.edition
    FROM demo_article_links AS l
    JOIN demo_articles AS a
      ON a.id = l.article_id AND a.edition = l.edition
    ORDER BY l.link_id`);
  console.table(result.rows);
} catch (error) {
  console.error(error.code ?? error.message);
  process.exitCode = 1;
} finally {
  try { await client.query('ROLLBACK'); }
  finally { await client.end(); }
}

Program meneruskan nilai artikel secara terpisah melalui $1, $2, dan placeholder berikutnya, mengikuti API kueri berparameter milik driver. Nama tabel dan kolom tetap ditetapkan dalam SQL; parameter PostgreSQL tidak dapat mewakili identifier. node-postgres: Queries

Jalankan program dengan pengaturan koneksi untuk basis data lokal Anda yang sudah ada. Perintah ini mengasumsikan nama basis datanya article_demo, server mendengarkan pada port 5432, dan pengaturan koneksi lokal default yang sudah ada berfungsi. Ubah nama basis data atau port jika perlu. node-postgres membaca variabel lingkungan koneksi PostgreSQL. node-postgres: Connecting

PGHOST=localhost PGPORT=5432 PGDATABASE=article_demo node demo.mjs

JavaScript tersebut lolos pemeriksaan sintaks saat artikel ini disiapkan. Perilaku basis data diperiksa berdasarkan dokumentasi; program ini tidak dijalankan pada server PostgreSQL di sini.

Apa yang ditunjukkan oleh kedua kesalahan dan kueri terakhir?

Dengan prasyarat yang disebutkan, pesan yang diharapkan adalah:

Expected error: 42830
Expected error: 23503

Kesalahan pertama terjadi saat membuat tabel anak, sebelum baris artikel apa pun dimasukkan. PostgreSQL mengidentifikasi 42830 sebagai invalid_foreign_key. Setelah tabel induk memperoleh kunci unik yang sesuai, definisi tabel anak yang sama menjadi valid. PostgreSQL: Error Codes

Kesalahan kedua terjadi saat memasukkan (101, 'evening'). Skemanya kini valid, tetapi pasangan yang diberikan tidak memiliki baris induk. PostgreSQL mengidentifikasi 23503 sebagai foreign_key_violation. Memeriksa kodenya membedakan ketidakcocokan baris ini dari ketidakcocokan skema sebelumnya tanpa bergantung pada teks kesalahan yang dilokalkan. PostgreSQL: Error Codes

Kueri terakhir seharusnya mengembalikan satu baris: tautan 1, judul Paper Shapes, edition morning. Kondisi join-nya mencakup kedua komponen relasi tersebut. Dengan demikian, kueri itu menjelaskan pasangan yang sama dengan yang ditegakkan oleh batasan.

Dua foreign key terpisah akan menyatakan aturan yang berbeda. Sekalipun edition memiliki kunci unik yang memenuhi syarat, referensi terpisah ke id dan edition akan mengizinkan setiap nilai cocok dengan baris induk yang berbeda. Contoh ini memperlihatkan perbedaan tersebut: kedua nilai secara terpisah dalam pasangan yang ditolak memang ada, tetapi kombinasinya tidak ada.

Perintah terakhir ROLLBACK sengaja membuang seluruh demonstrasi, termasuk definisi tabelnya. Untuk menyesuaikan desain ini dengan tabel permanen, buat perubahan skema melalui proses migrasi biasa; menyalin program tabel sementara ini tidak akan membuat skema aplikasi yang bertahan lama.

Apa yang perlu diperiksa sebelum mengubah skema yang sudah ada?

Mulailah dengan daftar kolom yang dirujuk. Identifikasi kunci induk yang benar-benar mencakup daftar tersebut, alih-alih mengandalkan nama indeks atau fakta bahwa baris saat ini kebetulan memiliki nilai yang berbeda. Untuk contoh ini, tambahan yang diperlukan adalah batasan UNIQUE (id, edition) bernama yang ditampilkan dalam program.

Menambahkan batasan unik akan membuat indeks B-tree unik. Pertimbangkan indeks tambahan tersebut saat memilih antara desain komposit dan hanya menyimpan article_id. Deklarasi foreign key itu sendiri tidak otomatis membuat indeks pada kolom anak. PostgreSQL: Constraints

Sebelum menambahkan foreign key ke tabel anak yang sudah ada, periksa apakah pasangan yang tersimpan cocok dengan baris induk. Kunci target yang valid memenuhi persyaratan skema; kunci tersebut tidak memperbaiki nilai anak yang tidak cocok. ALTER TABLE ... ADD CONSTRAINT biasa akan memeriksa baris yang sudah ada. PostgreSQL juga mendukung penambahan foreign key sebagai NOT VALID, lalu menjalankan VALIDATE CONSTRAINT; cara ini menunda pemeriksaan baris yang sudah ada, sambil tetap menerapkan batasan pada penyisipan atau pembaruan berikutnya. PostgreSQL: ALTER TABLE

Perlakukan validasi sebagai masalah yang terpisah dari kelayakan target. NOT VALID tidak menyediakan kunci induk yang hilang. Opsi ini juga bukan cara untuk menambahkan batasan unik tanpa membuktikan keunikan: PostgreSQL 18 membatasi opsi tersebut pada batasan foreign key, check, dan not-null. PostgreSQL: ALTER TABLE

Kondisi apa yang dapat mengubah hasilnya?

Indeks unik parsial tidak memenuhi syarat sebagai target foreign key biasa yang dijelaskan di sini. Demikian pula, menjadikan batasan unik yang dirujuk dapat ditangguhkan tidak memenuhi persyaratan batasan yang tidak dapat ditangguhkan sebagaimana didokumentasikan. Gunakan UNIQUE (id, edition) default yang sederhana untuk contoh ini. PostgreSQL: CREATE TABLE

Penanganan nilai null juga penting. Dengan perilaku pencocokan default, foreign key komposit tidak mewajibkan kecocokan induk jika salah satu komponen yang dirujuk bernilai null. Contoh ini menandai kedua komponen anak sebagai NOT NULL agar setiap pasangan yang disimpan harus cocok. Jika relasi opsional memang diinginkan, MATCH FULL mengizinkan referensi yang seluruh nilainya null, tetapi menolak referensi yang hanya sebagian nilainya null. PostgreSQL: Constraints

Pengujian yang berguna adalah terhadap pasangan itu sendiri: dapatkah tabel anak menyebut pengenal artikel dan edition yang berasal dari baris berbeda? Jika hal itu harus gagal, pertahankan relasi komposit dan berikan kunci induk yang sesuai. Jika tabel anak hanya perlu mengidentifikasi artikel, rujuk id dan ambil edition dari artikel tersebut saat melakukan kueri.

Sumber