如何在 SQLAlchemy 中為循環關係實現複雜的外鍵約束？

SQLAlchemy 中的複雜外鍵約束

在資料庫建模領域，經常會遇到外鍵關係形成複雜依賴關係的情況。其中一個場景涉及確保循環關係中外鍵引用的有效性，同時保持引用完整性。

問題

讓我們考慮兩個表：SystemVariables 和 VariableOptions 。 SystemVariables 包含可設定變量，而 VariableOptions 包含每個變數的可能選項。每個 SystemVariable 都有一個引用所選選項的 choice_id，每個 VariableOption 都有指示其關聯變數的variable_id。

挑戰在於實作一個資料庫約束，以確保 SystemVariables 中的 choice_id 引用中的有效（variable_id）選項變數選項。

解決方案

解決此挑戰的一種方法是擴展引用所選選項的外鍵以包含 choice_id 和variable_id。這確保了 SystemVariables 中的 choice_id 也隱式驗證了variable_id。

實作

以下 SQL腳本示範了這個方法：

CREATE TABLE systemvariables (
  variable_id int PRIMARY KEY,
  choice_id   int,
  variable    text
);

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY,
  variable_id int REFERENCES systemvariables ON UPDATE CASCADE ON DELETE CASCADE,
  option      text,
  UNIQUE (option_id, variable_id)  -- needed for the FK
);

ALTER TABLE systemvariables
  ADD CONSTRAINT systemvariables_choice_id_fk
  FOREIGN KEY (choice_id, variable_id) REFERENCES variableoptions(option_id, variable_id);

This設計保證choice_id的有效性可以透過variable_id外鍵來驗證

所有鍵列不為空

避免具有未知關聯的條目的另一種方法是使所有鍵列（包括外鍵）不為空。然而，這種傳統方法引入了循環依賴問題。

可延遲外鍵約束

為了解決循環依賴，PostgreSQL 提供了一個解決方案：可延遲外鍵約束。透過將外鍵約束定義為可延遲，它們的驗證被推遲到交易結束。

這允許以任意順序靈活輸入變數和選項，克服與循環依賴相關的先有雞還是先有蛋的問題。

CREATE TABLE systemvariables (
  variable_id int PRIMARY KEY,
  variable    text,
  choice_id   int NOT NULL
);

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY,
  option      text,
  variable_id int NOT NULL REFERENCES systemvariables
     ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  UNIQUE (option_id, variable_id) -- needed for the foreign key
);

ALTER TABLE systemvariables
ADD CONSTRAINT systemvariables_choice_id_fk FOREIGN KEY (choice_id, variable_id)
   REFERENCES variableoptions(option_id, variable_id) DEFERRABLE INITIALLY DEFERRED;

在此設計中，外鍵約束被推遲，允許在單一事務中靈活輸入數據，同時仍保持引用完整性。

以上是如何在 SQLAlchemy 中為循環關係實現複雜的外鍵約束？的詳細內容。更多資訊請關注PHP中文網其他相關文章！