Kunci asing komposit PostgreSQL memerlukan kunci unik yang layak dan meliputi lajur yang dirujuk. Kunci utama pada id sahaja tidak melayakkan rujukan kepada (id, edition), walaupun secara logiknya ia menjadikan pasangan itu unik. PostgreSQL: CREATE TABLE

Panduan ini membantu pembangun yang bekerja dalam Terminal pada Mac mendiagnosis ketidakpadanan itu, memilih hubungan yang sesuai dan menguji hasilnya dengan node-postgres. Semua baris contoh menerangkan artikel sintetik. SQL ini menyasarkan PostgreSQL 18 dan kunci asing biasa berasaskan kesamaan; PostgreSQL 18 mendokumentasikan peraturan berasingan untuk rujukan temporal menggunakan PERIOD. PostgreSQL: CREATE TABLE

Mengapa PostgreSQL menolak rujukan komposit itu?

Katakan jadual artikel mempunyai tiga lajur: id, edition dan title. Kunci utamanya ialah id. Jadual lain menyimpan rujukan artikel sebagai dua nilai: article_id dan edition.

Hubungan yang dimaksudkan dinyatakan melalui cebisan kekangan jadual ini:

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

Pengisytiharan itu meminta PostgreSQL menyemak kedua-dua nilai bersama-sama terhadap satu baris induk. Sasaran yang didokumenkan mestilah lajur dalam kekangan unik atau kunci utama yang tidak boleh ditangguhkan, atau lajur dalam indeks unik yang bukan separa. Kunci utama sedia ada hanya meliputi id; rujukan itu menyatakan (id, edition). PostgreSQL: CREATE TABLE

Hujah matematik di sebalik bantahan itu boleh difahami: jika tiada dua baris induk yang boleh berkongsi id, baris-baris itu juga tidak boleh berkongsi pasangan (id, edition). Namun begitu, implikasi itu tidak menyediakan sasaran yang diisytiharkan sepadan seperti yang diperlukan oleh takrif kunci asing. Bagi skema ini, menambah UNIQUE (id, edition) menyediakannya. Kekangan tambahan itu berlebihan dari segi logik untuk keunikan, tetapi berfungsi sebagai sasaran rujukan komposit yang konkrit.

Pertimbangkan baris induk ilustrasi ini:

id edition title
101 morning Paper Shapes
102 evening Color Notes

Pasangan anak (101, 'morning') mempunyai padanan. Pasangan (101, 'evening') tidak mempunyai padanan: kedua-dua nilainya terdapat dalam jadual, tetapi pada baris yang berbeza. Perbezaan inilah sebabnya hubungan komposit digunakan apabila kedua-dua nilai yang disimpan mesti menerangkan artikel yang sama.

Patutkah anda menambah kunci unik atau memudahkan kunci asing?

Pilih hubungan sebelum mengubah skema. Soalan yang tepat ialah sama ada anak perlu menyimpan dan menguatkuasakan edisi bersama-sama dengan pengecam artikel.

Tingkah laku yang diperlukan Reka bentuk yang sesuai Kesan
Anak mengenal pasti artikel dan memaparkan edisi semasanya Simpan article_id; rujuk induk id Baca edisi daripada induk semasa membuat pertanyaan
Anak menyimpan edisi yang mesti sepadan dengan artikelnya Kekalkan rujukan komposit; tambahkan UNIQUE (id, edition) pada induk PostgreSQL menyemak bahawa pasangan yang disimpan wujud bersama-sama
Nombor artikel yang sama boleh mengenal pasti beberapa baris edisi Modelkan identiti induk sebagai (id, edition) Kunci utama pada id sahaja akan menghalang baris induk tersebut

Baris terakhir menerangkan model data yang berbeza. Jangan buang kunci utama satu lajur semata-mata untuk menghilangkan ralat: tentukan dahulu sama ada nombor artikel berulang benar-benar patut ada dalam jadual induk. PostgreSQL membenarkan kunci utama komposit, dan sesebuah jadual hanya boleh mempunyai satu kunci utama. PostgreSQL: Constraints

Dalam contoh boleh jalan di bawah, pengecam artikel kekal unik secara global. Anak sengaja menyimpan edisi juga, jadi induk mengekalkan PRIMARY KEY (id) dan mendapat pasangan unik tambahan.

Bagaimanakah anda boleh mengulangi dan membetulkan ralat pada Mac?

Gunakan pangkalan data PostgreSQL 18 setempat yang sedia ada dan pemasangan Node.js yang berfungsi; panduan ini mengandaikan kedua-duanya sudah tersedia. Buka Terminal dan cipta direktori projek baharu. Pasang pemacu menggunakan arahan pakej yang didokumenkan. 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 mencipta jadual sementara, menunjukkan skema yang ditolak, menambah kunci yang sepadan dan menguji pasangan artikel yang sah serta tidak sah. Semua pernyataan menggunakan satu klien kerana node-postgres memerlukan transaksi kekal pada tika klien yang sama. node-postgres: Transactions

Fungsi pembantu menggunakan savepoint bagi setiap kegagalan yang dijangka supaya transaksi boleh diteruskan. PostgreSQL mendokumenkan ROLLBACK TO SAVEPOINT sebagai tindakan membatalkan arahan selepas savepoint itu dan membolehkan kerja disambung semula. 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 ini menghantar nilai artikel secara berasingan melalui $1, $2 dan pemegang tempat seterusnya, mengikut API pertanyaan berparameter pemacu tersebut. Nama jadual dan lajur kekal tetap dalam SQL; parameter PostgreSQL tidak boleh mewakili pengecam. node-postgres: Queries

Jalankan program dengan tetapan sambungan untuk pangkalan data setempat sedia ada anda. Penggunaan ini mengandaikan nama pangkalan data ialah article_demo, pelayan mendengar pada port 5432 dan tetapan sambungan setempat lalai sedia ada anda berfungsi. Tukar nama pangkalan data atau port jika perlu. node-postgres membaca pemboleh ubah persekitaran sambungan PostgreSQL. node-postgres: Connecting

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

JavaScript itu lulus semakan sintaks semasa penyediaan artikel ini. Tingkah laku pangkalan data disemak berdasarkan dokumentasi; program ini tidak dijalankan pada pelayan PostgreSQL di sini.

Apakah yang ditunjukkan oleh kedua-dua ralat dan pertanyaan akhir?

Dengan prasyarat yang dinyatakan, mesej yang dijangka ialah:

Expected error: 42830
Expected error: 23503

Ralat pertama berlaku semasa mencipta jadual anak, sebelum sebarang baris artikel dimasukkan. PostgreSQL mengenal pasti 42830 sebagai invalid_foreign_key. Selepas induk mendapat kunci unik yang sepadan, takrif jadual anak yang sama menjadi sah. PostgreSQL: Error Codes

Ralat kedua berlaku semasa memasukkan (101, 'evening'). Skema kini sah, tetapi pasangan yang diberikan tidak mempunyai baris induk. PostgreSQL mengenal pasti 23503 sebagai foreign_key_violation. Menyemak kod membezakan ketidakpadanan baris ini daripada ketidakpadanan skema yang terdahulu tanpa bergantung pada teks ralat yang dilokalkan. PostgreSQL: Error Codes

Pertanyaan akhir sepatutnya memulangkan satu baris: pautan 1, tajuk Paper Shapes, edisi morning. Syarat cantumannya merangkumi kedua-dua komponen hubungan itu. Dengan itu, pertanyaan tersebut menerangkan pasangan yang sama seperti yang dikuatkuasakan oleh kekangan.

Dua kunci asing berasingan akan menyatakan peraturan yang berbeza. Walaupun edition mempunyai kunci uniknya sendiri yang layak, rujukan berasingan kepada id dan edition akan membenarkan setiap nilai dipadankan dengan baris induk yang berbeza. Contoh ini menjadikan perbezaan itu jelas: kedua-dua nilai secara berasingan dalam pasangan yang ditolak itu wujud, tetapi gabungannya tidak.

ROLLBACK yang terakhir sengaja memadamkan demonstrasi itu, termasuk takrif jadualnya. Untuk menyesuaikan reka bentuk ini kepada jadual kekal, buat perubahan skema melalui proses migrasi biasa anda; menyalin program jadual sementara ini tidak akan menghasilkan skema aplikasi yang berkekalan.

Apakah yang perlu diperiksa sebelum mengubah skema sedia ada?

Mulakan dengan senarai lajur yang dirujuk. Kenal pasti kunci induk yang benar-benar meliputi senarai itu, bukannya bergantung pada nama indeks atau hakikat bahawa baris semasa kebetulan berbeza. Bagi contoh ini, penambahan yang diperlukan ialah kekangan UNIQUE (id, edition) bernama yang ditunjukkan dalam program.

Menambah kekangan unik akan mencipta indeks B-tree unik. Ambil kira indeks tambahan itu apabila memilih antara reka bentuk komposit dengan menyimpan article_id sahaja. Pengisytiharan kunci asing itu sendiri tidak mencipta indeks secara automatik pada lajur anak. PostgreSQL: Constraints

Sebelum menambah kunci asing pada jadual anak sedia ada, semak sama ada pasangan yang disimpan sepadan dengan baris induk. Kunci sasaran yang sah memenuhi keperluan skema; ia tidak membaiki nilai anak yang tidak sepadan. ALTER TABLE ... ADD CONSTRAINT biasa menyemak baris sedia ada. PostgreSQL juga menyokong penambahan kunci asing sebagai NOT VALID dan kemudian menjalankan VALIDATE CONSTRAINT; cara ini menangguhkan semakan baris sedia ada sambil menguatkuasakan kekangan pada sisipan atau kemas kini seterusnya. PostgreSQL: ALTER TABLE

Anggap pengesahan sebagai isu berasingan daripada kelayakan sasaran. NOT VALID tidak menyediakan kunci induk yang hilang. Ia juga bukan cara untuk menambah kekangan unik tanpa membuktikan keunikan: PostgreSQL 18 mengehadkan pilihan itu kepada kekangan kunci asing, semak dan bukan nol. PostgreSQL: ALTER TABLE

Sempadan manakah yang boleh mengubah hasilnya?

Indeks unik separa tidak layak sebagai sasaran kunci asing biasa yang diterangkan di sini. Begitu juga, menjadikan kekangan unik yang dirujuk boleh ditangguhkan tidak memenuhi keperluan kekangan tidak boleh ditangguhkan yang didokumenkan. Gunakan UNIQUE (id, edition) lalai yang mudah untuk contoh ini. PostgreSQL: CREATE TABLE

Pengendalian nilai null juga penting. Dengan tingkah laku padanan lalai, kunci asing komposit tidak memerlukan padanan induk apabila mana-mana komponen rujukan bernilai null. Contoh ini menetapkan kedua-dua komponen anak kepada NOT NULL supaya setiap pasangan yang disimpan mesti sepadan. Jika hubungan pilihan dikehendaki, MATCH FULL membenarkan rujukan yang semuanya null sambil menolak rujukan yang sebahagiannya null. PostgreSQL: Constraints

Ujian yang berguna ialah pada pasangan itu sendiri: bolehkah anak menamakan pengecam artikel dan edisi yang berasal daripada baris berbeza? Jika hal itu mesti gagal, kekalkan hubungan komposit dan sediakan kunci induk yang sepadan. Jika anak hanya perlu mengenal pasti artikel, rujuk id dan dapatkan edisi daripada artikel itu semasa membuat pertanyaan.

Sumber