如何在 SQLAlchemy 中實作複雜的外鍵約束以確保資料完整性？-mysql教程-PHP中文網

如何在 SQLAlchemy 中實作複雜的外鍵約束以確保資料完整性？

DDD

發布： 2024-12-20 06:26:09

原創

311 人瀏覽過

How to Implement Complex Foreign Key Constraints in SQLAlchemy for Data Integrity?

SQLAlchemy 中的複雜外鍵約束

概述

在兩個表之間創建外鍵關係有時會帶來挑戰，尤其是在處理循環引用時並確保資料完整性。本文解決了涉及複雜外鍵約束的特定場景，該約束確保兩個表中都存在引用資料。

問題陳述

考慮兩個表：SystemVariables 和 VariableOptions。 SystemVariables 表示系統變量，而 VariableOptions 列出這些變數的可能選項。每個變數選項都有一個外鍵指向 SystemVariables 中對應的變數。反過來，每個系統變數都有一個外鍵引用 VariableOptions 中目前選擇的選項。

問題的產生是因為需要額外的資料庫約束來強制 SystemVariables 中的每個 choice_id 引用 VariableOptions 中的有效選項。本質上，choice_id和variable_id之間必須有直接關係。

建議的解決方案

解決方案在於擴展引用所選選項的外鍵以包含choice_id和variable_id。這可確保兩個表中都存在引用。

以下是如何在 PostgreSQL 9.1 中實現此功能的範例：

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

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

登入後複製

此擴充外鍵約束有效地強制執行所需的資料完整性規則。插入或更新違反此規則的記錄將導致錯誤。

延遲外鍵約束

要處理兩個外鍵皆可延遲的情況，需要稍微不同的方法。在 PostgreSQL 中，DEFERRABLE INITIALLY DEFERRED 子句支援可延遲外鍵約束。這允許在兩個表中插入相關條目，而無需立即強制執行外鍵約束。在交易結束時檢查約束，解決了先有雞還是先有蛋的問題。

以下是如何實現這一點的範例：

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

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY NOT NULL,
  option      text NOT NULL,
  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;

登入後複製

這種方法確保了外國即使以任意順序插入相關條目，金鑰也會被強制執行。