Een PostgreSQL-foreign key met NOT VALID slaat de eerste controle van bestaande rijen over, maar dwingt nieuwe verwijzingen wel af. Oudere niet-overeenkomende rijen blijven bestaan totdat ze zijn hersteld en gevalideerd. PostgreSQL 17: ALTER TABLE

Deze handleiding is bedoeld voor een ontwikkelaar die in Terminal op een Mac werkt, een foreign key aan gevulde tabellen wil toevoegen en de gevolgen daarvan wil begrijpen. Het volledige node-postgres-voorbeeld gebruikt uitsluitend synthetische artikelgegevens en volgt één niet-overeenkomende verwijzing vanaf het aanmaken van de constraint tot en met het herstel en een geslaagde validatie.

Wat gebeurt er met bestaande rijen en nieuwe schrijfacties?

Denk aan twee tabellen: articles bevat artikel-ID's en titels, en article_links bevat verwijzingen naar die ID's. Een link komt niet overeen wanneer de niet-nullwaarde van article_id geen bijbehorend artikel heeft. Door de foreign key met NOT VALID toe te voegen, kan die oudere afwijking blijven bestaan terwijl de constraint voor volgende schrijfacties wordt ingesteld. PostgreSQL 17: ALTER TABLE

Bewerking na het toevoegen van de constraint Resultaat in dit voorbeeld
Een oudere niet-overeenkomende link ongewijzigd laten Deze blijft opgeslagen
Een link invoegen die naar een bestaand artikel verwijst Dit slaagt
Een link invoegen die naar een ontbrekend artikel verwijst Dit mislukt
De artikel-ID van een link wijzigen in een andere ontbrekende ID Dit mislukt
Valideren terwijl er een niet-overeenkomende link bestaat De validatie mislukt
Alle niet-overeenkomende verwijzingen herstellen en daarna valideren De validatie kan slagen

Deze uitkomsten volgen uit het gedocumenteerde onderscheid tussen het toevoegen en valideren van de constraint. PostgreSQL 17: ALTER TABLE

Een belangrijke uitzondering betreft updates waarbij de waarde van de foreign key ongewijzigd blijft. In PostgreSQL 18 kan de implementatie de controle van de foreign key overslaan wanneer de oude en nieuwe sleutels gelijk zijn. Daardoor kan het wijzigen van alleen het label van een oudere niet-overeenkomende link slagen zonder de verwijzing te herstellen. De implementatie bevat ook een speciaal geval voor een rij die in de huidige transactie is ingevoegd; generaliseer dit dus niet naar elke updatevolgorde. Implementatie van foreign keys in PostgreSQL 18

Het praktische gevolg is dat geslaagde labelwijzigingen geen goed bewijs zijn dat de opschoning is afgerond. Controleer niet-overeenkomende verwijzingen rechtstreeks en gebruik validatie om vast te stellen dat de constraint voor de hele tabel geldt.

Welke PostgreSQL-versie en tabelstructuur zijn hiervoor nodig?

Het onderstaande voorbeeld gebruikt gewone, niet-gepartitioneerde tabellen en is bedoeld voor PostgreSQL 17 en 18. Voor gepartitioneerde tabellen geldt een belangrijk versieverschil: PostgreSQL 17 staat expliciet niet toe dat foreign keys op gepartitioneerde tabellen als NOT VALID worden gedeclareerd; PostgreSQL 18 introduceerde deze mogelijkheid. Controleer de serverversie in plaats van ondersteuning af te leiden uit de geïnstalleerde Node-package. PostgreSQL 17: ALTER TABLE, releaseopmerkingen van PostgreSQL 18

Voor een gepartitioneerde verwijzende tabel op PostgreSQL 17 is het ondersteunde rechtstreekse pad om de verwijzingen te corrigeren en de foreign key normaal toe te voegen, zodat de eerste validatiescan wordt uitgevoerd. Als gefaseerde validatie op de gepartitioneerde bovenliggende tabel essentieel is, biedt PostgreSQL 18 de gedocumenteerde mogelijkheid. In deze handleiding blijft de tabelstructuur eenvoudig, zodat details over de partitiehiërarchie het gedrag van de rijen niet verhullen.

Ook voor de gerefereerde sleutel is een geschikte uniciteitsgarantie nodig. Hier is articles.article_id een primary key. De verwijzende kolom mag bewust NULL bevatten: volgens de standaardregel MATCH SIMPLE hoeft een nullverwijzing niet naar een bestaand artikel te verwijzen. Voeg afzonderlijk NOT NULL toe als elke link een artikel moet aanduiden. PostgreSQL 18: CREATE TABLE

Voer een volledig synthetisch voorbeeld uit op je Mac

Je hebt een actieve PostgreSQL-database, een bestaande werkende lokale verbindingsconfiguratie en een door node-postgres ondersteunde versie van Node.js nodig. Voer in een projectmap in Terminal de onderstaande opdracht uit om de package te installeren en sla het volledige programma vervolgens op als not-valid-demo.mjs. De officiële introductie van node-postgres beschrijft hoe je de package installeert. Documentatie van node-postgres

npm install pg

Het programma gebruikt new pg.Client(), dat omgevingsvariabelen voor de PostgreSQL-verbinding uitleest, zoals PGHOST, PGPORT, PGDATABASE en PGUSER. Gebruik de instellingen waarmee je al verbinding maakt met je lokale database. node-postgres: verbinding maken

Beide tabellen zijn tijdelijk en bestaan alleen tijdens de sessie van deze verbinding. PostgreSQL vereist dat foreign keys op tijdelijke tabellen verwijzen naar tijdelijke tabellen; daarom maakt het programma beide kanten tegelijk aan. PostgreSQL 18: CREATE TABLE

Elke bewerking gebruikt een afzonderlijke await-query. Waarden worden doorgegeven via parameters van node-postgres, zoals $1 en $2; de tabel- en constraintnamen staan vast in de SQL. node-postgres: queries

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();
}

Voer het opgeslagen programma vanuit dezelfde projectmap uit:

node not-valid-demo.mjs

De JavaScript-code doorstond tijdens de voorbereiding een syntaxiscontrole voor Node. Uitvoering op een database was niet beschikbaar in de onderzoeksomgeving; de volgende resultaten zijn gebaseerd op het gedocumenteerde gedrag en komen niet uit een vastgelegde uitvoerlog.

Hoe interpreteer je de resultaten?

Eerst hoort de constraint zichtbaar te zijn met convalidated ingesteld op false. De catalogus definieert dat veld als aanduiding of de constraint is gevalideerd. De query filtert op zowel tabel- als constraintnaam, omdat constraintnamen niet per se uniek zijn binnen de database. PostgreSQL: pg_constraint

Vervolgens horen zowel het invoegen van de nieuwe niet-overeenkomende verwijzing als het wijzigen van een verwijzing naar een niet-bestaand artikel 23503 op te leveren. PostgreSQL duidt die SQLSTATE aan als foreign_key_violation en raadt aan foutcodes te controleren in plaats van de vertaalde fouttekst te vergelijken. De helper accepteert alleen de verwachte code en constraint; bij onverwachte fouten stopt het programma. PostgreSQL-foutcodes

De update die alleen het label wijzigt, laat link 11 naar artikel 99 verwijzen. De diagnostische query hoort daarom precies die link terug te geven, nu met het label Revised label. De expliciete voorwaarde IS NOT NULL sluit optionele nullverwijzingen uit, terwijl NOT EXISTS niet-null-ID's opspoort die ontbreken in articles. Dit zijn illustratieve resultaten op basis van de opgegeven synthetische rijen.

Op dat moment hoort de validatie te mislukken. Nadat de herstelbewerking link 11 laat verwijzen naar artikel 1, hoort de validatie te slagen en hoort convalidated de waarde true te krijgen. VALIDATE CONSTRAINT controleert de eerder niet-gevalideerde constraint door de tabel te scannen; het voert zelf geen herstel uit. PostgreSQL: ALTER TABLE

Het herstel is een expliciete redactionele keuze voor deze demonstratie: beide links horen naar Desk arrangement te verwijzen. Controleer voor je eigen artikelgegevens elke niet-overeenkomende verwijzing voordat je beslist of je de ID corrigeert, het ontbrekende artikel toevoegt of een verouderde link verwijdert. Elke niet-overeenkomende ID automatisch naar één artikel omleiden zou verschillen verhullen die de database zelf niet kan vaststellen.

Hoe pas je deze reeks toe op gevulde tabellen?

Werk in drie afzonderlijke fasen: voeg de constraint NOT VALID toe en commit deze, controleer en herstel niet-overeenkomende verwijzingen en voer daarna de validatie uit. Het toevoegen van de foreign key verkrijgt een SHARE ROW EXCLUSIVE-vergrendeling op beide tabellen. Tijdens de validatie krijgt de verwijzende tabel een SHARE UPDATE EXCLUSIVE-vergrendeling en de gerefereerde tabel een ROW SHARE-vergrendeling, zodat gewone updates tijdens de validatiescan gelijktijdig kunnen doorgaan. Het verkrijgen van vergrendelingen kan nog steeds op zich laten wachten. PostgreSQL: ALTER TABLE

Door het aanvankelijk toevoegen en de latere validatie in afzonderlijke transacties te houden, behoud je het doel van de gefaseerde aanpak: de eerste transactie kan afronden voordat de langere scan begint. Plan het toevoegen als een korte bewerking en gebruik de diagnostische query als hulpmiddel bij het opschonen, met een geslaagde validatie als afrondende stap.

De demonstratie vermijdt bewust een expliciet transactieblok, zodat verwachte fouten per statement kunnen worden afgehandeld. Als je de reeks aanpast voor transacties in node-postgres, geef je zelf BEGIN, COMMIT en ROLLBACK op, en voer je elk statement binnen een transactie uit op dezelfde client. De documentatie van node-postgres vermeldt uitdrukkelijk dat je pool.query() niet moet gebruiken voor transactiestatements. node-postgres: transacties

Gebruik de drie observaties uit het voorbeeld samen: niet-overeenkomende rijen laten zien welk herstel nodig is, afgewezen nieuwe verwijzingen tonen aan dat de constraint nog steeds wordt afgedwongen en convalidated = true registreert een geslaagde validatie. Zo heb je een concreet eindpunt voor het toevoegen van de relatie aan gevulde artikeltabellen.

Bronnen