Kombinera ON DELETE SET NULL och ON DELETE CASCADE genom att tilldela varje åtgärd till en separat främmande nyckel. Den här genomgången använder syntetiska artiklar för att visa hur en samling kan tas bort utan att artiklarna försvinner, medan borttagning av en artikel tar bort dess avsnitt.

Uppgiften är att definiera beteendet, verifiera båda borttagningsvägarna från en Mac-dator i Terminal och upprepa kontrollerna med node-postgres. Exemplen använder vanliga främmande nycklar och tillfälliga tabeller, så varje körning har sina egna exempelrader.

Vilken relation ska använda respektive borttagningsåtgärd?

En borttagningsåtgärd hör till den främmande nyckeln i den refererande tabellen. SET NULL rensar referensen när den refererade raden försvinner; CASCADE tar bort den refererande raden. PostgreSQL dokumenterar valfria relationer som ett lämpligt användningsområde för SET NULL och beroende komponenter som ett lämpligt användningsområde för CASCADE. Begränsningar

I det här exemplet grupperar en samling artiklar, men en artikel kan finnas utan en samling. Ett avsnitt tillhör en artikel och har ingen självständig funktion. Det här är uttryckliga designval för exemplet:

Främmande nyckel Vald åtgärd Avsett resultat
demo_articles.collection_id refererar till demo_collections.collection_id ON DELETE SET NULL När en samling tas bort finns dess artiklar kvar utan en samlingsreferens.
demo_sections.article_id refererar till demo_articles.article_id ON DELETE CASCADE När en artikel tas bort tas dess avsnitt bort.

Det resulterande beteendet beror på var borttagningen börjar. När en samling tas bort ändras artikelns collection_id; artikeln tas inte bort. Därför finns det ingen borttagning av en artikel som avsnittens ON DELETE CASCADE kan reagera på. Om artikeln tas bort direkt aktiveras kaskaden. Detta följer av de dokumenterade definitionerna av de två åtgärderna. CREATE TABLE

Lägg inte till två konkurrerande främmande nycklar för samma relation för att uttrycka ”rensa referensen och ta bort raden”. Bestäm om den refererande raden ska finnas kvar och välj sedan den åtgärd som genomför det beslutet.

Skapa ett komplett exempel på din Mac

Du behöver en PostgreSQL-server som körs, en befintlig databas som du kan ansluta till och psql tillgängligt i Terminal. Kommandona nedan använder den befintliga lokala databasen postgres; byt ut namnet mot namnet på din befintliga databas om det behövs. Installation och serverkonfiguration ingår inte i den här genomgången.

Spara följande som en vanlig textfil med namnet setup.sql i en arbetsmapp. Alla tre tabeller är tillfälliga: PostgreSQL tar bort tillfälliga tabeller när deras session avslutas. Även alla refererade tabeller i det här exemplet är tillfälliga, eftersom PostgreSQL inte tillåter främmande nycklar mellan tillfälliga och permanenta tabeller. CREATE TABLE

CREATE TEMP TABLE demo_collections (
    collection_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TEMP TABLE demo_articles (
    article_id integer PRIMARY KEY,
    collection_id integer,
    title text NOT NULL,
    CONSTRAINT demo_articles_collection_fk
        FOREIGN KEY (collection_id)
        REFERENCES demo_collections (collection_id)
        ON DELETE SET NULL
);

CREATE TEMP TABLE demo_sections (
    section_id integer PRIMARY KEY,
    article_id integer NOT NULL,
    heading text NOT NULL,
    CONSTRAINT demo_sections_article_fk
        FOREIGN KEY (article_id)
        REFERENCES demo_articles (article_id)
        ON DELETE CASCADE
);

CREATE INDEX demo_articles_collection_idx
    ON demo_articles (collection_id);
CREATE INDEX demo_sections_article_idx
    ON demo_sections (article_id);

INSERT INTO demo_collections (collection_id, title) VALUES
    (1, 'Layout Notes'),
    (2, 'Color Notes');

INSERT INTO demo_articles (article_id, collection_id, title) VALUES
    (101, 1, 'Grid Spacing'),
    (102, 2, 'Color Pairings');

INSERT INTO demo_sections (section_id, article_id, heading) VALUES
    (1001, 101, 'Overview'),
    (1002, 101, 'Examples'),
    (1003, 102, 'Overview');

Observera att collection_id tillåter nullvärden, medan article_id i avsnittstabellen är obligatoriskt. Åtgärden SET NULL måste fortfarande uppfylla tabellens övriga begränsningar. De uttryckliga indexen täcker de refererande kolumnerna; PostgreSQL skapar inte automatiskt sådana index när en främmande nyckel deklareras. Begränsningar

Verifiera båda borttagningsvägarna i samma session

Spara nästa block som verify.sql bredvid setup.sql. Det är ett komplett psql-skript: \ir inkluderar installationsfilen relativt verifieringsskriptet. Kör båda filerna via det här skriptet så att de tillfälliga tabellerna finns kvar i samma session. Alternativet -f kör en fil, och ON_ERROR_STOP avbryter skriptet om ett fel uppstår. psql

BEGIN;
\ir setup.sql

DELETE FROM demo_collections
WHERE collection_id = 1
RETURNING collection_id;

SELECT a.article_id,
       a.title,
       a.collection_id,
       c.title AS collection_title,
       count(s.section_id) AS section_count
FROM demo_articles AS a
LEFT JOIN demo_collections AS c
    ON c.collection_id = a.collection_id
LEFT JOIN demo_sections AS s
    ON s.article_id = a.article_id
GROUP BY a.article_id, a.title, a.collection_id, c.title
ORDER BY a.article_id;

DELETE FROM demo_articles
WHERE article_id = 101
RETURNING article_id;

SELECT section_id, article_id, heading
FROM demo_sections
ORDER BY section_id;

ROLLBACK;

Gå till mappen som innehåller filerna i Terminal och kör:

psql -X -v ON_ERROR_STOP=1 -d postgres -f verify.sql

De förväntade resultaten från den första frågan härleds från exempelraderna:

Artikel Samlings-ID efter borttagning av samlingen Samlingens titel Antal avsnitt
101 NULL NULL 2
102 2 Color Notes 1

Artikel 101 finns kvar med sina två avsnitt. Efter den andra borttagningen ska avsnittsfrågan endast returnera avsnitt 1003, som tillhör artikel 102. De här två kontrollerna skiljer mellan att bevara en artikel och att ta bort en artikel och dess beroende rader.

Verifieringsfrågan använder avsiktligt LEFT JOIN. PostgreSQL behåller en omatchad rad från den vänstra tabellen och anger nullvärden för de saknade värdena från den högra tabellen. En inre koppling till samlingar skulle utelämna artikel 101 när dess referens blir null, vilket skulle göra att en bevarad artikel försvinner från frågeresultatet. Tabelluttryck

Satserna med RETURNING rapporterar rader som tagits bort från måltabellerna. De ger ingen samlad förteckning över alla avsnitt som tagits bort genom kaskaden; den separata avsnittsfrågan verifierar resultatet. DELETE

Den sista ROLLBACK avbryter ändringarna i demonstrationen. PostgreSQL behandlar även en fristående sats som en transaktion, så en uttrycklig transaktion är användbar här för att samla konfiguration, borttagning, granskning och återställning. Transaktioner

Kör samma kontroller med node-postgres

För JavaScript-versionen låter du setup.sql vara oförändrad. Använd Node.js 24 för det här exemplet och installera det dokumenterade paketet pg i arbetsmappen: node-postgres

npm install pg

Spara följande kompletta program som verify.mjs bredvid setup.sql. Det läser in samma syntetiska testdata, verifierar att borttagning av en samling bevarar båda artiklarna och alla tre avsnitten och verifierar sedan att borttagning av en artikel endast tar bort de matchande avsnitten.

Programmet använder en och samma anslutna klient under hela transaktionen, vilket krävs av node-postgres. Frågevärdena skickas via det dokumenterade gränssnittet med en parameterlista och $1. Transaktioner, Frågor

import pg from 'pg';
import { readFile } from 'node:fs/promises';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client();
const setup = await readFile(
  new URL('./setup.sql', import.meta.url),
  'utf8'
);

await client.connect();
try {
  await client.query('BEGIN');
  await client.query(setup);

  const removed = await client.query(
    'DELETE FROM demo_collections WHERE collection_id = $1 RETURNING collection_id',
    [1]
  );
  assert.deepEqual(removed.rows, [{ collection_id: 1 }]);

  const articles = await client.query(
    'SELECT article_id, collection_id FROM demo_articles ORDER BY article_id'
  );
  assert.deepEqual(articles.rows, [
    { article_id: 101, collection_id: null },
    { article_id: 102, collection_id: 2 }
  ]);

  const before = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(
    before.rows.map(row => row.section_id),
    [1001, 1002, 1003]
  );

  await client.query(
    'DELETE FROM demo_articles WHERE article_id = $1',
    [101]
  );
  const after = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(after.rows, [{ section_id: 1003 }]);

  console.log('Expected SET NULL and CASCADE outcomes verified.');
  await client.query('ROLLBACK');
} catch (error) {
  try {
    await client.query('ROLLBACK');
  } catch (rollbackError) {
    console.error('Rollback failed:', rollbackError.message);
  }
  throw error;
} finally {
  await client.end();
}

Kör programmet mot samma befintliga lokala databas:

PGDATABASE=postgres node verify.mjs

new Client() läser PostgreSQL:s anslutningsmiljövariabler. Behåll eventuella anslutningsinställningar som redan behövs för din lokala server; ett byte av enbart databasnamn konfigurerar inte en annan server. Ansluta

Ett misslyckat påstående leder till återställningsvägen och ett fel visas. Framgångsmeddelandet visas först när alla resultatkontroller har godkänts. Programmet återställer avsiktligt transaktionen även vid framgång, så att demonstrationen kan upprepas.

JavaScript-syntaxen kontrollerades under förberedelserna. Det fanns inget PostgreSQL-körbart program i undersökningsmiljön, så databasexemplen granskades mot dokumentationen i stället för att köras; tabellerna ovan visar de förväntade resultaten.

Vad förändras med sammansatta främmande nycklar?

Huvudexemplet använder främmande nycklar med en kolumn. En sammansatt främmande nyckel innebär ytterligare ett beslut: ska borttagningen rensa alla refererande kolumner eller bara den valfria? PostgreSQL 15 introducerade kolumnlistor för främmande nycklars ON DELETE SET-åtgärder; i tidigare versioner påverkades alla kolumner i den främmande nyckeln. Versionsinformation för PostgreSQL 15

En sammansatt artikelreferens kan till exempel innehålla en obligatorisk utgåveidentifierare och en valfri referens till en relaterad artikel. I PostgreSQL 15 eller senare kan ON DELETE SET NULL (related_article_id) bevara utgåveidentifieraren och samtidigt rensa den valfria artikelreferensen. Utan den kolumnlistan gäller SET NULL för båda kolumnerna. Den här syntaxen är ett PostgreSQL-tillägg och kan användas för ON DELETE, inte ON UPDATE. CREATE TABLE

Partiell nullsättning kräver också en kompatibel matchningsregel. Standardvärdet MATCH SIMPLE tillåter en sammansatt främmande nyckel som innehåller ett nullvärde; MATCH FULL avvisar en blandning av nullvärden och icke-nullvärden i de refererande kolumnerna. Att behålla en obligatorisk utgåveidentifierare medan endast den relaterade artikelidentifieraren nullas är därför inkompatibelt med MATCH FULL. Begränsningar

I PostgreSQL 14 kan du använda ett schema med en separat valfri referens till en kolumn, som i huvudexemplet, om det korrekt representerar relationen. Om den sammansatta relationen måste finnas kvar rensar du uttryckligen dess valfria referens innan den refererade raden tas bort, i en transaktion. Det är en sekvens som hanteras av applikationen, eftersom syntaxen med kolumnlista inte stöds.

Det finns ytterligare en dokumenterad begränsning i PostgreSQL 18: temporala främmande nycklar som använder PERIOD stöder inte CASCADE eller SET NULL. De vanliga främmande nycklarna som visas här är det alternativ som stöds för de här borttagningsåtgärderna när relationen inte omfattar täckning över en tidsperiod. CREATE TABLE

Hur felsöker du ett oväntat resultat?

Använd borttagningsvägen som felsökningssekvens:

  1. Bekräfta den refererande tabellen. Granska \d demo_articles och \d demo_sections i sessionen som innehåller tabellerna. Kontrollera de faktiska åtgärderna för de främmande nycklarna i stället för att dra slutsatser utifrån kolumnnamnen. PostgreSQL dokumenterar \d som ett sätt att granska tabelldetaljer och begränsningsnamn. Ändra tabeller
  2. Kontrollera nullbarhet och övriga begränsningar. Om borttagningen av en samling misslyckas kontrollerar du att kolumnen som rensas tillåter nullvärden och att övriga begränsningar godkänner den resulterande raden. SET NULL åsidosätter dem inte. Begränsningar
  3. Granska alla relevanta främmande nycklar. En annan relation kan ha en annan åtgärd. Om ingen åtgärd anges är standardvärdet NO ACTION, vilket kan hindra borttagningen om referenser finns kvar. CREATE TABLE
  4. Fråga bastabellen innan du tolkar en koppling. Om artikeln fortfarande finns där granskar du kopplingar och filter som kan utelämna dess nya nullreferens. Tabelluttryck
  5. Kör transaktionssatser på samma klient. I en poolad applikation hämtar du en klient för hela transaktionen. node-postgres avråder uttryckligen från att använda pool.query() för transaktionssatser. Transaktioner

För den här artikelmodellen är godkännandet konkret: när samling 1 tas bort finns artikel 101 och dess avsnitt kvar, medan borttagning av artikel 101 innebär att endast avsnittet tillhörande artikel 102 finns kvar. Behåll de här två kontrollerna tillsammans när du anpassar schemat, så att det avsedda beteendet för vad som ska finnas kvar respektive tas bort förblir tydligt.

Källor