Klucz obcy NOT VALID w PostgreSQL pomija początkowe sprawdzenie istniejących wierszy, jednocześnie wymuszając poprawność nowych odwołań. Starsze niedopasowane wiersze pozostają do czasu ich naprawienia i walidacji. PostgreSQL 17: ALTER TABLE

Ten przewodnik jest przeznaczony dla programisty pracującego w Terminalu na Macu, który chce dodać klucz obcy do tabel zawierających już dane i zrozumieć wyniki. Kompletny przykład z node-postgres korzysta wyłącznie ze sztucznych danych artykułów i śledzi jedno niedopasowane odwołanie od utworzenia ograniczenia, przez naprawę, aż po pomyślną walidację.

Co dzieje się z istniejącymi wierszami i nowymi zapisami?

Rozważmy dwie tabele: articles zawiera identyfikatory i tytuły artykułów, a article_links zawiera odwołania do tych identyfikatorów. Łącze jest niedopasowane, gdy jego niepuste article_id nie ma odpowiadającego mu artykułu. Dodanie klucza obcego z NOT VALID pozwala pozostawić to starsze niedopasowanie, ustanawiając jednocześnie ograniczenie dla kolejnych zapisów. PostgreSQL 17: ALTER TABLE

Operacja po dodaniu ograniczenia Wynik w tym przykładzie
Pozostawienie starszego niedopasowanego łącza bez zmian Pozostaje zapisane
Wstawienie łącza odwołującego się do istniejącego artykułu Powodzenie
Wstawienie łącza odwołującego się do brakującego artykułu Niepowodzenie
Zmiana identyfikatora artykułu w łączu na inny brakujący identyfikator Niepowodzenie
Walidacja, gdy pozostaje niedopasowane łącze Walidacja kończy się niepowodzeniem
Naprawienie wszystkich niedopasowanych odwołań, a następnie walidacja Walidacja może się powieść

Te wyniki wynikają z udokumentowanego rozdzielenia dodawania i walidacji ograniczenia. PostgreSQL 17: ALTER TABLE

Istotny wyjątek dotyczy aktualizacji, które nie zmieniają wartości klucza obcego. W PostgreSQL 18 implementacja może pominąć sprawdzenie klucza obcego, gdy stare i nowe klucze są równe. W rezultacie zmiana wyłącznie etykiety starszego niedopasowanego łącza może się powieść bez naprawienia odwołania. Implementacja zawiera też szczególny przypadek dla wiersza wstawionego przez bieżącą transakcję, więc nie należy uogólniać tego na każdą sekwencję aktualizacji. Implementacja kluczy obcych w PostgreSQL 18

Praktyczny wniosek jest taki, że pomyślna edycja etykiety nie świadczy o zakończeniu porządkowania danych. Sprawdź bezpośrednio niedopasowane odwołania i użyj walidacji, aby potwierdzić, że ograniczenie jest spełnione w całej tabeli.

Jakiej wersji PostgreSQL i struktury tabel wymaga ten przykład?

Poniższy przykład wykorzystuje zwykłe, niepartycjonowane tabele i jest przeznaczony dla PostgreSQL 17 i 18. Tabele partycjonowane mają istotną granicę między wersjami: PostgreSQL 17 wyraźnie zabrania deklarowania kluczy obcych jako NOT VALID na tabelach partycjonowanych; PostgreSQL 18 wprowadził taką możliwość. Sprawdź wersję serwera, zamiast wnioskować o obsłudze na podstawie zainstalowanego pakietu Node. PostgreSQL 17: ALTER TABLE, informacje o wydaniu PostgreSQL 18

W przypadku partycjonowanej tabeli odwołującej się w PostgreSQL 17 obsługiwana bezpośrednia ścieżka polega na poprawieniu odwołań i zwykłym dodaniu klucza obcego, co umożliwia początkowe skanowanie walidacyjne. Jeśli etapowa walidacja tabeli nadrzędnej z partycjami jest niezbędna, PostgreSQL 18 zapewnia udokumentowaną możliwość. Ten samouczek zachowuje prostą strukturę tabel, aby szczegóły hierarchii partycji nie przesłaniały zachowania wierszy.

Klucz, do którego prowadzi odwołanie, również musi mieć odpowiednie gwarancje unikalności. Tutaj articles.article_id jest kluczem głównym. Kolumna odwołująca się celowo dopuszcza NULL: zgodnie z domyślną regułą MATCH SIMPLE odwołanie o wartości null nie wymaga pasującego artykułu. Jeśli każde łącze musi wskazywać artykuł, dodaj osobno NOT NULL. PostgreSQL 18: CREATE TABLE

Uruchom kompletny przykład na sztucznych danych na Macu

Potrzebujesz działającej bazy PostgreSQL, istniejącej lokalnej konfiguracji połączenia oraz wersji Node.js obsługiwanej przez node-postgres. W katalogu projektu w Terminalu zainstaluj pakiet poleceniem poniżej, a następnie zapisz cały program jako not-valid-demo.mjs. Oficjalny wstęp do node-postgres opisuje instalację pakietu. Dokumentacja node-postgres

npm install pg

Program korzysta z new pg.Client(), które odczytuje zmienne środowiskowe połączenia z PostgreSQL, takie jak PGHOST, PGPORT, PGDATABASE i PGUSER. Użyj ustawień, które już pozwalają połączyć się z lokalną bazą danych. node-postgres: łączenie

Obie tabele są tymczasowe i istnieją tylko w ramach sesji tego połączenia. PostgreSQL wymaga, aby klucze obce w tabelach tymczasowych wskazywały na tabele tymczasowe, dlatego program tworzy obie strony relacji jednocześnie. PostgreSQL 18: CREATE TABLE

Każda operacja jest osobnym oczekiwanym zapytaniem. Wartości są przekazywane za pomocą parametrów node-postgres, takich jak $1 i $2; nazwy tabel i ograniczeń są ustalone w SQL. node-postgres: zapytania

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

Uruchom zapisany program z tego samego katalogu projektu:

node not-valid-demo.mjs

Podczas przygotowania tekst JavaScript przeszedł kontrolę składni. W środowisku badawczym nie było dostępu do wykonania zapytań w bazie; poniższe wyniki są oczekiwane na podstawie udokumentowanego działania, a nie pochodzą z zarejestrowanego przebiegu.

Jak interpretować wyniki?

Najpierw ograniczenie powinno pojawić się z wartością convalidated równą false. Katalog definiuje to pole jako informację o tym, czy ograniczenie zostało zwalidowane. Zapytanie filtruje wyniki według nazwy tabeli i ograniczenia, ponieważ nazwy ograniczeń nie muszą być unikalne w całej bazie danych. PostgreSQL: pg_constraint

Następnie wstawienie nowego niedopasowanego odwołania i zmiana odwołania na niedopasowane powinny zwrócić 23503. PostgreSQL identyfikuje ten kod SQLSTATE jako foreign_key_violation i zaleca sprawdzanie kodów błędów zamiast porównywania tekstu komunikatów, który może być zależny od języka. Funkcja pomocnicza akceptuje wyłącznie oczekiwany kod i ograniczenie; nieoczekiwane błędy przerywają działanie programu. Kody błędów PostgreSQL

Aktualizacja zmieniająca wyłącznie etykietę pozostawia łącze 11 wskazujące artykuł 99. Dlatego zapytanie diagnostyczne powinno zwrócić dokładnie to łącze, teraz z etykietą Revised label. Jawny warunek IS NOT NULL wyklucza opcjonalne odwołania o wartości null, a NOT EXISTS wykrywa niepuste identyfikatory nieobecne w articles. Są to przykładowe wyniki wyprowadzone z podanych sztucznych wierszy.

W tym momencie walidacja powinna się nie powieść. Po naprawie zmieniającej łącze 11, tak aby wskazywało artykuł 1, walidacja powinna się powieść, a convalidated powinno przyjąć wartość true. VALIDATE CONSTRAINT sprawdza wcześniej niezwalidowane ograniczenie, skanując tabelę; samo nie naprawia danych. PostgreSQL: ALTER TABLE

Naprawa jest w tej demonstracji jawną decyzją redakcyjną: oba łącza mają wskazywać na Desk arrangement. W przypadku własnych danych artykułów sprawdź każde niedopasowane odwołanie, zanim zdecydujesz, czy poprawić identyfikator, dodać brakujący artykuł, czy usunąć nieaktualne łącze. Automatyczne przekierowanie każdego niedopasowanego identyfikatora do jednego artykułu ukryłoby różnice, których baza danych nie potrafi rozstrzygnąć.

Jak zastosować tę sekwencję do tabel z istniejącymi danymi?

Wykonaj trzy odrębne etapy: dodaj i zatwierdź ograniczenie NOT VALID, sprawdź i napraw niedopasowane odwołania, a następnie przeprowadź walidację. Dodanie klucza obcego uzyskuje blokadę SHARE ROW EXCLUSIVE na obu tabelach. Walidacja uzyskuje blokadę SHARE UPDATE EXCLUSIVE na tabeli odwołującej się i ROW SHARE na tabeli wskazywanej, co pozwala na równoczesne zwykłe aktualizacje podczas skanowania walidacyjnego. Uzyskanie blokady może jednak wymagać oczekiwania. PostgreSQL: ALTER TABLE

Oddzielenie początkowego dodania od późniejszej walidacji w osobnych transakcjach pozwala zachować cel etapowego podejścia: pierwsza transakcja może się zakończyć, zanim rozpocznie się dłuższe skanowanie. Zaplanuj dodanie jako krótką operację, a zapytanie diagnostyczne potraktuj jako pomoc przy porządkowaniu danych; zakończeniem jest pomyślna walidacja.

Demonstracja celowo pomija jawny blok transakcji, aby oczekiwane niepowodzenia poszczególnych instrukcji można było obsłużyć osobno. Jeśli dostosowujesz tę sekwencję do transakcji node-postgres, samodzielnie wykonuj BEGIN, COMMIT i ROLLBACK, a wszystkie instrukcje w ramach jednej transakcji wysyłaj przez tego samego klienta. Dokumentacja node-postgres wyraźnie zaleca, by nie używać pool.query() do instrukcji transakcyjnych. node-postgres: transakcje

Korzystaj łącznie z trzech obserwacji z przykładu: niedopasowane wiersze wskazują zakres napraw, odrzucone nowe odwołania potwierdzają dalsze egzekwowanie ograniczenia, a convalidated = true odnotowuje pomyślną walidację. Dzięki temu wiesz konkretnie, kiedy zakończyć dodawanie relacji do tabel artykułów zawierających już dane.

Źródła