Złożony klucz obcy w PostgreSQL wymaga kwalifikującego się klucza unikalnego obejmującego kolumny, do których się odwołuje. Sam klucz główny id nie kwalifikuje się do odwołania do (id, edition), mimo że logicznie zapewnia unikalność tej pary. PostgreSQL: CREATE TABLE

Ten przewodnik pomoże deweloperowi pracującemu w Terminalu na Macu zdiagnozować tę niezgodność, wybrać odpowiednią relację i przetestować wynik za pomocą node-postgres. Wszystkie przykładowe wiersze opisują fikcyjne artykuły. Przykłady SQL są przeznaczone dla PostgreSQL 18 i zwykłych kluczy obcych opartych na równości; dokumentacja PostgreSQL 18 opisuje odrębne zasady dotyczące odwołań temporalnych z użyciem PERIOD. PostgreSQL: CREATE TABLE

Dlaczego PostgreSQL odrzuca złożone odwołanie?

Załóżmy, że tabela artykułów ma trzy kolumny: id, edition i title. Jej kluczem głównym jest id. Inna tabela przechowuje odwołanie do artykułu jako dwie wartości: article_id i edition.

Zamierzona relacja jest wyrażona przez ten fragment ograniczenia tabeli:

FOREIGN KEY (article_id, edition)
  REFERENCES demo_articles (id, edition)

Ta deklaracja nakazuje PostgreSQL sprawdzić obie wartości razem względem jednego wiersza tabeli nadrzędnej. Zgodnie z dokumentacją wskazane kolumny muszą należeć do nieodraczalnego ograniczenia unikalności lub klucza głównego albo do nieczęściowego indeksu unikalnego. Istniejący klucz główny obejmuje tylko id; odwołanie wskazuje (id, edition). PostgreSQL: CREATE TABLE

Matematyczne uzasadnienie tego zastrzeżenia jest zrozumiałe: jeśli żadne dwa wiersze tabeli nadrzędnej nie mogą mieć tego samego id, nie mogą też mieć tej samej pary (id, edition). Mimo to ta implikacja nie zapewnia zgodnego z deklaracją celu wymaganego przez definicję klucza obcego. W tym schemacie dodanie UNIQUE (id, edition) zapewnia taki cel. Dodatkowe ograniczenie jest logicznie zbędne dla unikalności, ale pełni konkretną funkcję celu złożonego odwołania.

Rozważmy te przykładowe wiersze tabeli nadrzędnej:

id edition title
101 morning Paper Shapes
102 evening Color Notes

Para podrzędna (101, 'morning') ma dopasowanie. Para (101, 'evening') go nie ma: obie wartości występują w tabeli, ale w różnych wierszach. Dlatego warto użyć złożonej relacji, gdy obie zapisane wartości muszą opisywać ten sam artykuł.

Czy dodać klucz unikalny, czy uprościć klucz obcy?

Wybierz relację przed zmianą schematu. Najważniejsze pytanie brzmi, czy tabela podrzędna musi przechowywać i weryfikować wydanie wraz z identyfikatorem artykułu.

Wymagane zachowanie Odpowiedni projekt Konsekwencja
Tabela podrzędna identyfikuje artykuł i wyświetla jego bieżące wydanie Zapisuj article_id; odwołuj się do id w tabeli nadrzędnej Pobieraj wydanie z tabeli nadrzędnej podczas zapytania
Tabela podrzędna przechowuje wydanie, które musi być zgodne z artykułem Zachowaj złożone odwołanie; dodaj UNIQUE (id, edition) do tabeli nadrzędnej PostgreSQL sprawdza, czy zapisana para występuje razem
Ten sam numer artykułu może identyfikować kilka wierszy wydań Zdefiniuj tożsamość wiersza nadrzędnego jako (id, edition) Sam klucz główny id uniemożliwiłby istnienie takich wierszy nadrzędnych

Ostatni wiersz opisuje inny model danych. Nie usuwaj jednokolumnowego klucza głównego tylko po to, by pozbyć się błędu: najpierw ustal, czy powtarzające się numery artykułów rzeczywiście powinny występować w tabeli nadrzędnej. PostgreSQL dopuszcza złożony klucz główny, a tabela może mieć tylko jeden klucz główny. PostgreSQL: Constraints

W poniższym przykładzie, który można uruchomić, identyfikatory artykułów pozostają globalnie unikalne. Tabela podrzędna celowo przechowuje również wydanie, dlatego tabela nadrzędna zachowuje PRIMARY KEY (id) i zyskuje dodatkową unikalną parę.

Jak odtworzyć i naprawić błąd na Macu?

Użyj istniejącej lokalnej bazy danych PostgreSQL 18 i działającej instalacji Node.js; w tym przewodniku zakładamy, że obie są już dostępne. Otwórz Terminal i utwórz nowy katalog projektu. Zainstaluj sterownik poleceniem opisanym w dokumentacji. node-postgres: Welcome

mkdir composite-fk-demo
cd composite-fk-demo
npm init -y
npm install pg

Zapisz poniższy kompletny program jako demo.mjs. Tworzy on tabele tymczasowe, demonstruje odrzucony schemat, dodaje pasujący klucz i testuje prawidłowe oraz nieprawidłowe pary artykułów. Wszystkie instrukcje używają jednego klienta, ponieważ node-postgres wymaga, aby transakcja była obsługiwana przez tę samą instancję klienta. node-postgres: Transactions

Funkcja pomocnicza używa punktu zapisu dla każdego oczekiwanego błędu, dzięki czemu transakcja może być kontynuowana. Dokumentacja PostgreSQL wyjaśnia, że ROLLBACK TO SAVEPOINT cofa polecenia wykonane po tym punkcie i pozwala wznowić pracę. PostgreSQL: ROLLBACK TO SAVEPOINT

import pg from 'pg';
const { Client } = pg;
const client = new Client();

const childDDL = `CREATE TEMP TABLE demo_article_links (
  link_id integer PRIMARY KEY,
  article_id integer NOT NULL,
  edition text NOT NULL,
  FOREIGN KEY (article_id, edition)
    REFERENCES demo_articles (id, edition)
)`;

async function expectError(sql, values, code) {
  await client.query('SAVEPOINT expected_error');
  try {
    await client.query(sql, values);
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT expected_error');
    await client.query('RELEASE SAVEPOINT expected_error');
    if (error.code !== code) throw error;
    console.log(`Expected error: ${code}`);
    return;
  }
  await client.query('ROLLBACK TO SAVEPOINT expected_error');
  await client.query('RELEASE SAVEPOINT expected_error');
  throw new Error(`Expected SQLSTATE ${code}, but query succeeded`);
}

try {
  await client.connect();
  await client.query('BEGIN');
  await client.query(`CREATE TEMP TABLE demo_articles (
    id integer PRIMARY KEY,
    edition text NOT NULL,
    title text NOT NULL
  )`);

  await expectError(childDDL, [], '42830');

  await client.query(`ALTER TABLE demo_articles
    ADD CONSTRAINT demo_articles_id_edition_key
    UNIQUE (id, edition)`);
  await client.query(childDDL);

  await client.query(`INSERT INTO demo_articles (id, edition, title)
    VALUES ($1, $2, $3), ($4, $5, $6)`,
    [101, 'morning', 'Paper Shapes', 102, 'evening', 'Color Notes']);

  await client.query(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [1, 101, 'morning']);

  await expectError(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [2, 101, 'evening'], '23503');

  const result = await client.query(`SELECT l.link_id, a.title, a.edition
    FROM demo_article_links AS l
    JOIN demo_articles AS a
      ON a.id = l.article_id AND a.edition = l.edition
    ORDER BY l.link_id`);
  console.table(result.rows);
} catch (error) {
  console.error(error.code ?? error.message);
  process.exitCode = 1;
} finally {
  try { await client.query('ROLLBACK'); }
  finally { await client.end(); }
}

Program przekazuje wartości artykułów osobno przez $1, $2 i kolejne symbole zastępcze, zgodnie z interfejsem zapytań parametryzowanych sterownika. Nazwy tabel i kolumn pozostają stałe w SQL; parametry PostgreSQL nie mogą zastępować identyfikatorów. node-postgres: Queries

Uruchom program, podając ustawienia połączenia z istniejącą lokalną bazą danych. To polecenie zakłada, że baza danych nazywa się article_demo, serwer nasłuchuje na porcie 5432, a domyślne lokalne ustawienia połączenia działają. W razie potrzeby zmień nazwę bazy danych lub port. node-postgres odczytuje zmienne środowiskowe połączenia PostgreSQL. node-postgres: Connecting

PGHOST=localhost PGPORT=5432 PGDATABASE=article_demo node demo.mjs

Kod JavaScript przeszedł kontrolę składni podczas przygotowywania tego artykułu. Zachowanie bazy danych sprawdzono na podstawie dokumentacji; program nie został tutaj uruchomiony na serwerze PostgreSQL.

Co pokazują oba błędy i końcowe zapytanie?

Przy podanych wymaganiach wstępnych oczekiwane komunikaty to:

Oczekiwany błąd: 42830
Oczekiwany błąd: 23503

Pierwszy błąd występuje podczas tworzenia tabeli podrzędnej, zanim zostaną wstawione jakiekolwiek wiersze artykułów. PostgreSQL oznacza 42830 jako invalid_foreign_key. Gdy tabela nadrzędna zyska pasujący klucz unikalny, ta sama definicja tabeli podrzędnej staje się prawidłowa. PostgreSQL: Error Codes

Drugi błąd występuje podczas wstawiania (101, 'evening'). Schemat jest już prawidłowy, ale dla podanej pary nie ma wiersza nadrzędnego. PostgreSQL oznacza 23503 jako foreign_key_violation. Sprawdzenie kodu pozwala odróżnić niezgodność wiersza od wcześniejszej niezgodności schematu bez polegania na lokalizowanym tekście błędu. PostgreSQL: Error Codes

Końcowe zapytanie powinno zwrócić jeden wiersz: link 1, tytuł Paper Shapes, wydanie morning. Warunek złączenia obejmuje oba składniki relacji. Dzięki temu zapytanie opisuje tę samą parę, którą egzekwuje ograniczenie.

Dwa osobne klucze obce wyrażałyby inną regułę. Nawet gdyby edition miał własny kwalifikujący się klucz unikalny, niezależne odwołania do id i edition pozwalałyby każdej wartości dopasować inny wiersz nadrzędny. Przykład pokazuje tę różnicę: obie pojedyncze wartości z odrzuconej pary istnieją, ale ich kombinacja już nie.

Końcowe ROLLBACK celowo usuwa całą zawartość demonstracji, w tym definicje tabel. Aby zastosować ten projekt do trwałych tabel, wprowadź zmianę schematu w ramach zwykłego procesu migracji; skopiowanie programu z tabelami tymczasowymi nie tworzy trwałego schematu aplikacji.

Co sprawdzić przed zmianą istniejącego schematu?

Zacznij od listy wskazanych kolumn. Ustal, który klucz tabeli nadrzędnej rzeczywiście obejmuje tę listę, zamiast polegać na nazwie indeksu lub na tym, że obecne wiersze są akurat różne. W tym przykładzie potrzebne jest dodanie nazwanego ograniczenia UNIQUE (id, edition) pokazanego w programie.

Dodanie ograniczenia unikalności tworzy unikalny indeks B-tree. Uwzględnij ten dodatkowy indeks, wybierając między złożonym projektem a przechowywaniem tylko article_id. Sama deklaracja klucza obcego nie tworzy automatycznie indeksu na kolumnach tabeli podrzędnej. PostgreSQL: Constraints

Przed dodaniem klucza obcego do istniejącej tabeli podrzędnej sprawdź, czy zapisane w niej pary pasują do wierszy tabeli nadrzędnej. Prawidłowy docelowy klucz rozwiązuje wymóg dotyczący schematu, ale nie naprawia niezgodnych wartości w tabeli podrzędnej. Zwykłe polecenie ALTER TABLE ... ADD CONSTRAINT sprawdza istniejące wiersze. PostgreSQL pozwala też dodać klucz obcy jako NOT VALID, a następnie wykonać VALIDATE CONSTRAINT; pozwala to odłożyć sprawdzanie istniejących wierszy, a jednocześnie egzekwować ograniczenie przy kolejnych wstawieniach lub aktualizacjach. PostgreSQL: ALTER TABLE

Traktuj walidację jako osobną kwestię od kwalifikowalności celu. NOT VALID nie zapewnia brakującego klucza tabeli nadrzędnej. Nie pozwala też dodać ograniczenia unikalności bez potwierdzenia unikalności: PostgreSQL 18 ogranicza tę opcję do kluczy obcych, ograniczeń sprawdzających i ograniczeń not-null. PostgreSQL: ALTER TABLE

Jakie wyjątki mogą zmienić wynik?

Częściowy indeks unikalny nie kwalifikuje się jako cel zwykłego klucza obcego opisanego tutaj. Podobnie odraczalne ograniczenie unikalności nie spełnia udokumentowanego wymogu nieodraczalności. W tym przykładzie użyj prostego ustawienia domyślnego UNIQUE (id, edition). PostgreSQL: CREATE TABLE

Znaczenie ma też obsługa wartości null. Przy domyślnym sposobie dopasowywania złożony klucz obcy nie wymaga dopasowania wiersza nadrzędnego, jeśli którykolwiek składnik wskazania ma wartość null. W przykładzie oba składniki w tabeli podrzędnej mają oznaczenie NOT NULL, więc każda zapisana para musi mieć dopasowanie. Jeśli relacja ma być opcjonalna, MATCH FULL dopuszcza odwołanie złożone w całości z wartości null, a odrzuca odwołanie częściowo null. PostgreSQL: Constraints

Przydatne pytanie dotyczy samej pary: czy tabela podrzędna może wskazać identyfikator artykułu i wydanie, które należą do różnych wierszy? Jeśli ma to być niedozwolone, zachowaj złożoną relację i zapewnij jej pasujący klucz w tabeli nadrzędnej. Jeśli tabela podrzędna musi tylko identyfikować artykuł, odwołuj się do id i pobieraj wydanie tego artykułu podczas zapytania.

Źródła