En PostgreSQL-främmande nyckel med NOT VALID hoppar över den inledande kontrollen av befintliga rader, samtidigt som nya referenser upprätthålls. Äldre omatchade rader finns kvar tills de har åtgärdats och validerats. PostgreSQL 17: ALTER TABLE

Den här guiden är avsedd för en utvecklare som arbetar i Terminal på en Mac och vill lägga till en främmande nyckel i tabeller som redan innehåller data och förstå resultatet. Det fullständiga node-postgres-exemplet använder enbart syntetiska artikeldata och följer en omatchad referens från att begränsningen skapas till dess att den åtgärdas och valideras.

Vad händer med befintliga rader och nya skrivningar?

Tänk dig två tabeller: articles innehåller artikel-ID:n och titlar, och article_links innehåller referenser till dessa ID:n. En länk är omatchad när dess icke-nullvärde article_id saknar motsvarande artikel. Om den främmande nyckeln läggs till med NOT VALID kan den äldre avvikelsen finnas kvar, samtidigt som begränsningen börjar gälla för efterföljande skrivningar. PostgreSQL 17: ALTER TABLE

Åtgärd efter att begränsningen har lagts till Resultat i det här exemplet
Lämna en äldre omatchad länk orörd Den finns kvar lagrad
Infoga en länk som refererar till en befintlig artikel Det lyckas
Infoga en länk som refererar till en artikel som saknas Det misslyckas
Ändra en länks artikel-ID till ett annat ID som saknas Det misslyckas
Validera medan en omatchad länk finns kvar Valideringen misslyckas
Åtgärda alla omatchade referenser och sedan validera Valideringen kan lyckas

Dessa resultat följer den dokumenterade åtskillnaden mellan att lägga till och validera begränsningen. PostgreSQL 17: ALTER TABLE

Ett viktigt undantag gäller uppdateringar där värdet för den främmande nyckeln inte ändras. I PostgreSQL 18 kan implementationen hoppa över kontrollen av den främmande nyckeln när den gamla och den nya nyckeln jämförs som lika. Därför kan det gå att ändra en äldre omatchad länks etikett utan att åtgärda referensen. Implementation innehåller också ett specialfall för en rad som infogats av den aktuella transaktionen, så detta ska inte generaliseras till alla uppdateringsförlopp. PostgreSQL 18-implementationen av främmande nycklar

Den praktiska följden är att lyckade etikettändringar är ett dåligt bevis på att rensningen är klar. Granska omatchade referenser direkt och använd validering för att fastställa att begränsningen gäller för hela tabellen.

Vilken PostgreSQL-version och tabellstruktur krävs?

Exemplet nedan använder vanliga, icke-partitionerade tabeller och är avsett för PostgreSQL 17 och 18. För partitionerade tabeller finns en viktig versionsgräns: PostgreSQL 17 tillåter uttryckligen inte att främmande nycklar på partitionerade tabeller deklareras som NOT VALID; PostgreSQL 18 införde den möjligheten. Kontrollera serverversionen i stället för att dra slutsatser om stödet utifrån det installerade Node-paketet. PostgreSQL 17: ALTER TABLE, Versionsnyheter för PostgreSQL 18

För en partitionerad refererande tabell i PostgreSQL 17 är den direkt stödda vägen att korrigera referenserna och lägga till den främmande nyckeln på vanligt sätt, så att den inledande valideringsgenomsökningen genomförs. Om stegvis validering av den partitionerade överordnade tabellen är nödvändig erbjuder PostgreSQL 18 den dokumenterade möjligheten. Den här handledningen håller tabellayouten enkel så att detaljer om partitionhierarkier inte skymmer radbeteendet.

Den refererade nyckeln måste också ha en lämplig unikhetsgaranti. Här är articles.article_id en primärnyckel. Den refererande kolumnen tillåter avsiktligt NULL: enligt standardregeln MATCH SIMPLE behöver en nullreferens inte ha någon matchande artikel. Lägg till NOT NULL separat om varje länk måste identifiera en artikel. PostgreSQL 18: CREATE TABLE

Kör ett fullständigt syntetiskt exempel från din Mac

Du behöver en körande PostgreSQL-databas, en befintlig fungerande lokal anslutningskonfiguration och en Node.js-version som stöds av node-postgres. Installera paketet med kommandot nedan i en projektkatalog i Terminal och spara sedan hela programmet som not-valid-demo.mjs. Den officiella node-postgres-introduktionen beskriver hur paketet installeras. Dokumentation för node-postgres

npm install pg

Programmet använder new pg.Client(), som läser PostgreSQL-miljövariabler för anslutning, exempelvis PGHOST, PGPORT, PGDATABASE och PGUSER. Använd de inställningar som redan ansluter till din lokala databas. node-postgres: Ansluta

Båda tabellerna är temporära och finns endast under den här anslutningens session. PostgreSQL kräver att främmande nycklar för temporära tabeller refererar till temporära tabeller, så programmet skapar båda sidorna tillsammans. PostgreSQL 18: CREATE TABLE

Varje åtgärd använder en separat väntad fråga. Värden skickas via node-postgres-parametrar som $1 och $2; tabell- och begränsningsnamnen är fasta i SQL-koden. node-postgres: Frågor

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

Kör det sparade programmet från samma projektkatalog:

node not-valid-demo.mjs

JavaScript-koden klarade en Node-syntaxkontroll under förberedelserna. Databaskörning var inte tillgänglig i undersökningsmiljön; resultaten nedan är förväntade utifrån det dokumenterade beteendet, inte hämtade från en faktisk körningslogg.

Hur ska du tolka resultaten?

Först bör begränsningen visas med convalidated satt till false. I katalogen definieras det fältet som huruvida begränsningen har validerats. Frågan filtrerar både på tabell- och begränsningsnamn eftersom begränsningsnamn inte nödvändigtvis är unika i databasen. PostgreSQL: pg_constraint

Därefter bör den nya omatchade infogningen och ändringen till en omatchad referens båda ge 23503. PostgreSQL identifierar den SQLSTATE-koden som foreign_key_violation och rekommenderar att man kontrollerar felkoder i stället för att matcha lokaliserad meddelandetext. Hjälpfunktionen godtar endast den förväntade koden och begränsningen; oväntade fel avbryter programmet. PostgreSQL-felkoder

Uppdateringen som bara ändrar etiketten gör att länk 11 fortfarande pekar på artikel 99. Diagnosfrågan bör därför returnera exakt den länken, nu med etiketten Revised label. Det uttryckliga villkoret IS NOT NULL utesluter valfria nullreferenser, medan NOT EXISTS identifierar icke-null-ID:n som saknas i articles. Detta är illustrativa resultat som härletts från de syntetiska raderna i exemplet.

Valideringen bör misslyckas i det här läget. När korrigeringen ändrar länk 11 så att den refererar till artikel 1, bör valideringen lyckas och convalidated bli true. VALIDATE CONSTRAINT kontrollerar den tidigare ovaliderade begränsningen genom att genomsöka tabellen; den utför ingen korrigering. PostgreSQL: ALTER TABLE

Korrigeringen är ett uttryckligt redaktionellt val i den här demonstrationen: båda länkarna är tänkta att referera till Desk arrangement. För dina egna artikeldata bör du granska varje omatchad referens innan du väljer om ID:t ska korrigeras, om den saknade artikeln ska skapas eller om en inaktuell länk ska tas bort. Att automatiskt omdirigera varje omatchat ID till en och samma artikel skulle dölja skillnader som databasen inte kan avgöra.

Hur tillämpar du förloppet på tabeller som redan innehåller data?

Använd tre separata steg: lägg till och verkställ NOT VALID-begränsningen, granska och åtgärda omatchade referenser och validera sedan. När den främmande nyckeln läggs till tas ett SHARE ROW EXCLUSIVE-lås på båda tabellerna. Validering tar SHARE UPDATE EXCLUSIVE på den refererande tabellen och ROW SHARE på den refererade tabellen, vilket tillåter vanliga samtidiga uppdateringar under valideringsgenomsökningen. Det kan ändå uppstå väntan på lås. PostgreSQL: ALTER TABLE

Om den första tilläggningen och den senare valideringen hålls i separata transaktioner bevaras syftet med den stegvisa metoden: den inledande transaktionen kan avslutas innan den längre genomsökningen börjar. Planera tilläggningen som en kort åtgärd och använd diagnosfrågan som hjälp vid rensningen; lyckad validering är det avslutande steget.

Demonstrationen undviker avsiktligt ett uttryckligt transaktionsblock så att förväntade satsfel kan hanteras var för sig. Om du anpassar förloppet till node-postgres-transaktioner ska du själv utfärda BEGIN, COMMIT och ROLLBACK och köra varje sats i transaktionen via samma klient. Dokumentationen för node-postgres säger uttryckligen att du inte ska använda pool.query() för transaktionssatser. node-postgres: Transaktioner

Använd de tre observationerna från exemplet tillsammans: omatchade rader identifierar arbetet som ska utföras, avvisade nya referenser visar att begränsningen fortsätter att upprätthållas och convalidated = true visar att valideringen har lyckats. Det ger dig en konkret slutpunkt för att lägga till relationen i artikel-tabeller som redan innehåller data.

Källor