Foreign key PostgreSQL NOT VALID melewati pemeriksaan awal terhadap baris yang sudah ada, sambil tetap menerapkan pemeriksaan pada referensi baru. Baris lama yang tidak cocok tetap ada hingga diperbaiki dan divalidasi. PostgreSQL 17: ALTER TABLE

Panduan ini ditujukan bagi developer yang bekerja di Terminal pada Mac, ingin menambahkan foreign key ke tabel yang sudah berisi data, dan memahami hasilnya. Contoh node-postgres lengkap ini hanya menggunakan data artikel sintetis dan mengikuti satu referensi yang tidak cocok, mulai dari pembuatan constraint hingga perbaikan dan validasi yang berhasil.

Apa yang terjadi pada baris yang sudah ada dan penulisan baru?

Pertimbangkan dua tabel: articles berisi ID dan judul artikel, sedangkan article_links berisi referensi ke ID tersebut. Sebuah tautan tidak cocok jika article_id-nya tidak null dan tidak memiliki artikel yang sesuai. Menambahkan foreign key dengan NOT VALID memungkinkan ketidakcocokan lama itu tetap ada sekaligus memberlakukan constraint untuk penulisan berikutnya. PostgreSQL 17: ALTER TABLE

Operasi setelah constraint ditambahkan Hasil dalam contoh ini
Membiarkan tautan lama yang tidak cocok tanpa perubahan Tetap tersimpan
Menyisipkan tautan yang merujuk ke artikel yang ada Berhasil
Menyisipkan tautan yang merujuk ke artikel yang tidak ada Gagal
Mengubah ID artikel pada tautan menjadi ID lain yang tidak ada Gagal
Memvalidasi saat masih ada tautan yang tidak cocok Validasi gagal
Memperbaiki semua referensi yang tidak cocok, lalu memvalidasi Validasi dapat berhasil

Hasil ini sesuai dengan pemisahan yang didokumentasikan antara penambahan dan validasi constraint. PostgreSQL 17: ALTER TABLE

Ada pengecualian penting untuk pembaruan yang tidak mengubah nilai foreign key. Di PostgreSQL 18, implementasi dapat melewati pemeriksaan foreign key jika kunci lama dan baru bernilai sama. Akibatnya, mengubah hanya label pada tautan lama yang tidak cocok dapat berhasil tanpa memperbaiki referensinya. Implementasi juga memiliki kasus khusus untuk baris yang disisipkan oleh transaksi saat ini, jadi perilaku ini tidak berlaku untuk semua urutan pembaruan. Implementasi foreign key PostgreSQL 18

Konsekuensi praktisnya, keberhasilan mengubah label bukan bukti yang memadai bahwa pembersihan telah selesai. Periksa referensi yang tidak cocok secara langsung, dan gunakan validasi untuk memastikan constraint berlaku di seluruh tabel.

Versi PostgreSQL dan bentuk tabel apa yang diperlukan?

Contoh di bawah menggunakan tabel biasa yang tidak dipartisi dan ditujukan untuk PostgreSQL 17 dan 18. Tabel yang dipartisi memiliki batas versi yang penting: PostgreSQL 17 secara eksplisit melarang deklarasi foreign key pada tabel yang dipartisi dengan NOT VALID; PostgreSQL 18 memperkenalkan kemampuan tersebut. Periksa versi server alih-alih menyimpulkan dukungan dari paket Node yang terpasang. PostgreSQL 17: ALTER TABLE, Catatan rilis PostgreSQL 18

Untuk tabel referensi yang dipartisi pada PostgreSQL 17, jalur langsung yang didukung adalah memperbaiki referensinya lalu menambahkan foreign key secara normal, sehingga pemindaian validasi awal dilakukan. Jika validasi bertahap pada tabel induk yang dipartisi diperlukan, PostgreSQL 18 menyediakan kemampuan yang terdokumentasi. Tutorial ini menggunakan tata letak tabel sederhana agar detail hierarki partisi tidak mengaburkan perilaku baris.

Kunci yang dirujuk juga memerlukan jaminan keunikan yang sesuai. Di sini, articles.article_id adalah kunci primer. Kolom referensi sengaja mengizinkan NULL: berdasarkan aturan default MATCH SIMPLE, referensi null tidak memerlukan artikel yang cocok. Tambahkan NOT NULL secara terpisah jika setiap tautan harus mengidentifikasi sebuah artikel. PostgreSQL 18: CREATE TABLE

Jalankan contoh sintetis lengkap dari Mac Anda

Anda memerlukan database PostgreSQL yang sedang berjalan, konfigurasi koneksi lokal yang sudah ada dan berfungsi, serta versi Node.js yang didukung node-postgres. Di direktori proyek pada Terminal, pasang paket dengan perintah di bawah, lalu simpan program lengkap sebagai not-valid-demo.mjs. Pengantar resmi node-postgres menjelaskan cara memasang paket. Dokumentasi node-postgres

npm install pg

Program menggunakan new pg.Client(), yang membaca variabel lingkungan koneksi PostgreSQL seperti PGHOST, PGPORT, PGDATABASE, dan PGUSER. Gunakan pengaturan yang sudah terhubung ke database lokal Anda. node-postgres: Menghubungkan

Kedua tabel bersifat sementara dan hanya ada selama sesi koneksi ini. PostgreSQL mewajibkan foreign key pada tabel sementara untuk merujuk ke tabel sementara, jadi program membuat kedua sisi secara bersamaan. PostgreSQL 18: CREATE TABLE

Setiap operasi menggunakan kueri terpisah yang ditunggu hingga selesai. Nilai diberikan melalui parameter node-postgres seperti $1 dan $2; nama tabel dan constraint ditetapkan langsung di SQL. node-postgres: Kueri

import pg from 'pg';

const client = new pg.Client({ connectionTimeoutMillis: 5000 });
const validate = `ALTER TABLE pg_temp.article_links
  VALIDATE CONSTRAINT article_links_article_fk`;

async function expectForeignKeyError(label, sql, values = []) {
  try {
    await client.query(sql, values);
  } catch (error) {
    if (error.code !== '23503' ||
        error.constraint !== 'article_links_article_fk') throw error;
    console.log(label + ': 23503 (expected)');
    return;
  }
  throw new Error(label + ': unexpectedly succeeded');
}

async function showStatus(label) {
  const result = await client.query(`
    SELECT conname, convalidated
    FROM pg_constraint
    WHERE conrelid = 'pg_temp.article_links'::regclass
      AND conname = 'article_links_article_fk'
      AND contype = 'f'
  `);
  console.log(label, result.rows);
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows);
  await client.query(`CREATE TEMP TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
  )`);
  await client.query(`CREATE TEMP TABLE article_links (
    link_id integer PRIMARY KEY,
    article_id integer,
    label text NOT NULL
  )`);
  await client.query(
    'INSERT INTO pg_temp.articles VALUES ($1, $2)',
    [1, 'Desk arrangement']
  );
  await client.query(`INSERT INTO pg_temp.article_links VALUES
    (10, 1, 'Matching article'),
    (11, 99, 'Older unmatched reference')`);
  await client.query(`ALTER TABLE pg_temp.article_links
    ADD CONSTRAINT article_links_article_fk
    FOREIGN KEY (article_id)
    REFERENCES pg_temp.articles (article_id) NOT VALID`);
  await showStatus('After adding');

  await expectForeignKeyError(
    'New unmatched reference',
    'INSERT INTO pg_temp.article_links VALUES ($1, $2, $3)',
    [12, 99, 'New reference']
  );
  await expectForeignKeyError(
    'Changed unmatched reference',
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [98, 11]
  );
  await client.query(
    'UPDATE pg_temp.article_links SET label = $1 WHERE link_id = $2',
    ['Revised label', 11]
  );
  console.log('Label-only update succeeded');

  const unmatched = await client.query(`
    SELECT l.link_id, l.article_id, l.label
    FROM pg_temp.article_links AS l
    WHERE l.article_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM pg_temp.articles AS a
        WHERE a.article_id = l.article_id
      )
    ORDER BY l.link_id
  `);
  console.log('Unmatched references', unmatched.rows);
  await expectForeignKeyError('Validation before repair', validate);
  await showStatus('After failed validation');

  await client.query(
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [1, 11]
  );
  await client.query(validate);
  await showStatus('After successful validation');
} finally {
  await client.end();
}

Jalankan program yang telah disimpan dari direktori proyek yang sama:

node not-valid-demo.mjs

JavaScript tersebut lolos pemeriksaan sintaks Node saat persiapan. Eksekusi database tidak tersedia di lingkungan riset; hasil berikut merupakan hasil yang diharapkan berdasarkan perilaku yang didokumentasikan, bukan log eksekusi yang direkam.

Bagaimana sebaiknya Anda menafsirkan hasilnya?

Pertama, constraint seharusnya muncul dengan convalidated bernilai false. Katalog mendefinisikan kolom itu sebagai penanda apakah constraint telah divalidasi. Kueri memfilter berdasarkan nama tabel dan constraint karena nama constraint belum tentu unik di seluruh database. PostgreSQL: pg_constraint

Berikutnya, penyisipan baru yang tidak cocok dan referensi tidak cocok yang diubah masing-masing seharusnya menghasilkan 23503. PostgreSQL mengidentifikasi SQLSTATE tersebut sebagai foreign_key_violation dan menyarankan pemeriksaan kode error alih-alih mencocokkan teks pesan yang dapat dilokalkan. Helper hanya menerima kode dan constraint yang diharapkan; kegagalan tak terduga akan menghentikan program. Kode error PostgreSQL

Pembaruan label saja membuat tautan 11 tetap menunjuk ke artikel 99. Karena itu, kueri diagnostik seharusnya mengembalikan tepat tautan tersebut, yang kini berlabel Revised label. Kondisi eksplisit IS NOT NULL mengecualikan referensi null yang bersifat opsional, sedangkan NOT EXISTS mengidentifikasi ID non-null yang tidak ditemukan dalam articles. Ini adalah hasil ilustratif yang diturunkan dari baris sintetis yang disediakan.

Pada tahap itu, validasi seharusnya gagal. Setelah perbaikan mengubah tautan 11 agar merujuk ke artikel 1, validasi seharusnya berhasil dan convalidated seharusnya berubah menjadi true. VALIDATE CONSTRAINT memeriksa constraint yang sebelumnya belum divalidasi dengan memindai tabel; perintah ini tidak memperbaiki data. PostgreSQL: ALTER TABLE

Perbaikan ini merupakan pilihan editorial eksplisit untuk demonstrasi ini: kedua tautan dimaksudkan untuk merujuk ke Desk arrangement. Untuk data artikel Anda sendiri, tinjau setiap referensi yang tidak cocok sebelum memutuskan apakah akan memperbaiki ID, menyediakan artikel yang hilang, atau menghapus tautan usang. Mengalihkan setiap ID yang tidak cocok secara otomatis ke satu artikel akan menyamarkan perbedaan yang tidak dapat ditentukan oleh database.

Bagaimana menerapkan tahapan ini pada tabel yang sudah berisi data?

Gunakan tiga tahap terpisah: tambahkan dan commit constraint NOT VALID, periksa dan perbaiki referensi yang tidak cocok, lalu validasi. Penambahan foreign key mengambil lock SHARE ROW EXCLUSIVE pada kedua tabel. Validasi mengambil SHARE UPDATE EXCLUSIVE pada tabel referensi dan ROW SHARE pada tabel yang dirujuk, sehingga pembaruan biasa secara bersamaan tetap dapat dilakukan selama pemindaian validasi. Namun, pengambilan lock tetap bisa menunggu. PostgreSQL: ALTER TABLE

Memisahkan penambahan awal dan validasi berikutnya ke dalam transaksi yang berbeda mempertahankan tujuan pendekatan bertahap: transaksi awal dapat selesai sebelum pemindaian yang lebih lama dimulai. Jadwalkan penambahan sebagai operasi singkat dan gunakan kueri diagnostik sebagai bantuan pembersihan, dengan validasi yang berhasil sebagai langkah penyelesaian.

Demonstrasi ini sengaja tidak menggunakan blok transaksi eksplisit agar kegagalan pernyataan yang diperkirakan dapat ditangani satu per satu. Jika Anda menyesuaikan tahapan ini untuk transaksi node-postgres, jalankan sendiri BEGIN, COMMIT, dan ROLLBACK, serta pastikan setiap pernyataan dalam transaksi menggunakan klien yang sama. Dokumentasi node-postgres secara eksplisit menyatakan agar pool.query() tidak digunakan untuk pernyataan transaksi. node-postgres: Transaksi

Gunakan bersama tiga pengamatan dari contoh: baris yang tidak cocok menunjukkan pekerjaan perbaikan, penolakan referensi baru menunjukkan bahwa penegakan tetap berlangsung, dan convalidated = true mencatat keberhasilan validasi. Dengan demikian, Anda memiliki titik akhir yang jelas untuk menambahkan relasi ke tabel artikel yang sudah berisi data.

Sumber