當限制為一對多時，在一對多對多的橋接關係中實施唯一性

June 3, 2021

我們定義了一系列配置，在 RESTful API 的驅動下，最終使用者可以建構新的修訂版。配置的某些組件可以有多個值；修訂涉及具有一對多關係的多個表。
因為配置是在其他地方發貨的，所以修訂被標記為已部署，並且變得不可變。如果使用者想要對配置進行更改，他們必須創建一個新版本（可以從現有版本中複製）。每個配置一個 版本可以標記為“目前”；這允許使用者隨意在過去的版本之間切換，或者通過不選擇任何版本來完全禁用配置。目前版本已部署，當將不同的修訂標記為“目前”時，您將替換已部署的配置。
我們已經準備好執行已部署修訂的不變性；當您第一次使用修訂作為目前修訂時，該deployed列會自動轉換到，並且所有進一步的 ,以及與修訂相關表中已部署修訂 id 匹配的行的操作都被阻止。TRUE``INSERT``UPDATE``DELETE
但是，用於公共名稱表中name列的任何值在所有目前配置的所有“目前”修訂版中都**必須是唯一的。**我正在嘗試找出執行此操作的最佳策略。
如果這是從配置到公共名稱的簡單的一對多關係，則可以通過對name列使用唯一約束來解決。相反，這是一個一對多的模式，revision充當橋接表，並將一對多對多的current_revision_id關係“折疊”成虛擬的一對多關係，從配置到公共名稱。
下面是一組簡化的表格來說明我們的情況：
-- Configurations
CREATE TABLE config (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    current_revision_id INT
);

-- Have multiple revisions
CREATE TABLE revision (
    id INT PRIMARY KEY,
    config_id INT NOT NULL REFERENCES config(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    description VARCHAR,
    foo INT NOT NULL,
    bar BOOLEAN NOT NULL,
    deployed BOOLEAN NOT NULL DEFAULT FALSE
);

-- A configuration has one _current_ revision
ALTER TABLE config
  ADD CONSTRAINT current_revision_id_fk
  FOREIGN KEY (current_revision_id)
  REFERENCES revision(id);
  
-- Revisions are automatically numbered in a view
CREATE VIEW numbered_revision AS (
    SELECT *,
    row_number() OVER (
        PARTITION BY config_id
        ORDER BY created_at, id
    ) AS number
    FROM revision
);

-- Configurations have multiple 'public names'
CREATE TABLE public_name (
    id INT PRIMARY KEY,
    revision_id INT NOT NULL REFERENCES revision(id),
    name VARCHAR(100),

    UNIQUE (revision_id, name)
);
該視圖僅用於為每個配置提供無間隙編號的修訂（永遠不會刪除修訂）。
作為 ERD 圖：
一些範例數據來說明設置：
INSERT INTO config (id, name) VALUES
  (17, 'config_foo'),
  (42, 'config_bar');
  
INSERT INTO revision (id, config_id, created_at, description, foo, bar) VALUES
  (11, 17, '2021-05-29 09:07:18', 'Foo configuration, first draft', 81, TRUE),
  (19, 17, '2021-05-29 10:42:17', 'Foo configuration, second draft', 73, TRUE),
  (23, 42, '2021-05-29 09:36:52', 'Bar configuration, first draft', 118, FALSE);

INSERT INTO public_name (id, revision_id, name) VALUES
  -- public names for foo configuration, first draft
  (83, 11, 'some.name'),
  (84, 11, 'other.name'),
  -- public names for foo configuration, second draft
  (85, 19, 'revised.name'),
  (86, 19, 'other.name'),
  (87, 19, 'third.name'),
  -- public names for bar configuration, first draft;
  -- some of the names here are the same used by foo configurations
  (88, 23, 'some.name'),
  (89, 23, 'unique.name'),
  (90, 23, 'other.name');
  
-- Foo configuration has a current, published revision:
UPDATE config SET current_revision_id = 19 WHERE id = 17;
UPDATE revision SET deployed = TRUE WHERE id in (11, 19);
這是一個顯示範例數據集的查詢：
SELECT
  c.name AS config,
  rev.number AS revision,
  rev.deployed,
  CASE WHEN c.current_revision_id = rev.id 
    THEN 'ACTIVE'
    ELSE ''
  END AS status,
  string_agg(p.name, ', ' ORDER BY p.name) AS names
FROM config c
JOIN numbered_revision AS rev ON c.id = rev.config_id
JOIN public_name p ON p.revision_id = rev.id
GROUP BY c.id, rev.id, rev.number, rev.deployed
ORDER BY c.id, rev.number;
db<>在這裡擺弄
在上面的輸出表中，第二行代表一個“目前”修訂版，公開部署），並且該行已被授予對列中公共名稱的獨占訪問權限names。
第三行代錶帶有草稿修訂的配置。任何將其設置為目前for 的嘗試都config_bar應該失敗，因為該名稱other.name已用於config_foo, 修訂版 2。如果將來config_foo要創建一個不包含的新修訂版，則other.name只有這樣才能config_bar使修訂版 1 成為目前版本。
我們確實預先驗證了這個約束；當不滿足先決條件時，API 會執行一些檢查並阻止將配置標記為目前配置。表中的名稱public_name也被限制為每個修訂版的唯一名稱 ( UNIQUE (revision_id, name))。這些都不能防止競爭條件，它們只是降低了競爭條件發生的速度。
我希望 CONSTRAINT TRIGGER on config，UPDATE在列的 s上觸發current_revision_id，足以強制執行此約束：
CREATE OR REPLACE FUNCTION unique_current_names() RETURNS trigger
  LANGUAGE plpgsql AS
$$BEGIN
  IF EXISTS (
    SELECT 1
    FROM public_name p
    WHERE
      p.revision_id = NEW.current_revision_id
    AND p.name IN (
      SELECT pp.name 
      FROM config AS pc
      JOIN public_name pp ON pp.revision_id = pc.current_revision_id
       AND pc.id != OLD.id
    )
  ) THEN
     RAISE EXCEPTION 'Public name is already published';
  END IF;

  RETURN NEW;
END;$$;

DROP TRIGGER IF EXISTS unique_current_names_trig ON config;
CREATE CONSTRAINT TRIGGER unique_current_names_trig
  AFTER UPDATE OF current_revision_id ON config
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE PROCEDURE unique_current_names();
config（注意和之間的關係public_name，一般情況下是多對多的連接，但更具體的current_revision_id情況是一對多的連接，你可以用config.current_version_id = public_name.version_id直接列出名字。）
我擔心的是，即使此觸發器在事務的最後觸發，仍然存在競爭條件的可能性，其中另一個連接也試圖使具有衝突的公共名稱的修訂成為目前版本。
OTOH，因為所有更新和插入都是 RESTFul API 操作的結果，所以永遠不會有包含多個操作（更新public_name和設置）的事務。current_revision_id這足以防止這裡的比賽條件，還是我錯過了一些極端情況？
另一種選擇可能是將目前修訂的公共名稱複製到單獨的“已發布名稱”表中（使用觸發器；刪除所有舊名稱，插入所有新名稱），並在其中的 name 列上具有 UNIQUE 約束。這會比約束觸發器更好嗎？
請注意，我們不能使用名稱空間或其他名稱（在公共網際網路上是主機名）來使它們唯一。一旦部署，名稱必須完全獨立。
我們知道該設計允許配置引用revision_id屬於不同配置的電流。這是我們在應用程序級別明確防範的一種可能性，但觸發器也可以處理這種情況。

我沒有遵循您的數據模型的確切細節，但是除非您使用事務隔離級別進行操作，否則延遲約束觸發器始終會受到競爭條件的影響。SERIALIZABLE
原因是並發更新config可能會導致觸發器函式在兩個會話中並行執行，在這些會話中它們看不到另一個事務的效果，因為還沒有事務送出。將觸發器定義為INITIALLY DEFERRED縮小了競爭條件的視窗，但不會關閉它。
作為使用的替代方法SERIALIZABLE，您可以修改您的觸發器函式，以便它使用鎖來防止它同時執行多次。事務級別的諮詢鎖是一種簡單的方法來做到這一點。

該解決方案的總體構想是添加一些非規範化，以便可以使用正常外鍵、檢查約束和過濾的唯一索引來強制執行所需的限制。

我不太了解 Postgres，所以這是一個 SQL Server 實現。

主要特點是：

目前修訂版從配置移到修訂版
如果不是目前版本，則目前版本為零，否則匹配版本 ID
目前版本通過級聯同步非規範化為公共名稱
使用過濾索引實現的棘手唯一性約束

這種設計可能會或可能不會被接受，但也許它至少會引起一些思考。

DROP TABLE IF EXISTS dbo.PublicNames, dbo.Revisions, dbo.Configs;

配置

CREATE TABLE dbo.Configs (
   ConfigID integer NOT NULL,
   ConfigName varchar(100) NOT NULL,

   CONSTRAINT [PK dbo.Configs ConfigID]
       PRIMARY KEY CLUSTERED (ConfigID),

   -- Assuming configuration names are unique
   CONSTRAINT [UQ dbo.Configs ConfigName]
       UNIQUE NONCLUSTERED (ConfigName),
);

修訂

CREATE TABLE dbo.Revisions (
   RevisionID integer NOT NULL,
   ConfigID integer NOT NULL,
   CurrentRevisionID integer NOT NULL 
       -- Revision is current if CurrentRevisionID = RevisionID
       -- Zero otherwise (see check constraints)
       DEFAULT 0,

   CreatedAt datetimeoffset NOT NULL 
       DEFAULT SYSDATETIMEOFFSET(),
   RevisionDescription varchar(200) NULL,
   Foo integer NOT NULL,
   Bar bit NOT NULL,

   -- Convenient computed column, persisted or not
   IsCurrent AS CONVERT(bit, 
       IIF(CurrentRevisionID = RevisionID, 'true', 'false')),
   IsDeployed bit NOT NULL 
       DEFAULT CONVERT(bit, 'false'),

   CONSTRAINT [PK dbo.Revisions RevisionID] 
       PRIMARY KEY CLUSTERED (RevisionID),

   CONSTRAINT [FK dbo.Revisions -&gt; dbo.Configs ConfigID]
       FOREIGN KEY (ConfigID)
           REFERENCES dbo.Configs (ConfigID),

   CONSTRAINT [CK dbo.Revisions Valid CreatedAt]
       CHECK (CreatedAt &lt;= SYSDATETIMEOFFSET()),

   -- RevisionID = 0 is a reserved value, must not be used
   CONSTRAINT [CK dbo.Revisions Valid RevisionID] 
       CHECK (RevisionID != 0),

   -- CurrentRevisionID must be zero or match RevisionID
   CONSTRAINT [CK dbo.Revisions Valid CurrentRevisionID] 
       CHECK (CurrentRevisionID IN (0, RevisionID)),

   -- A revision can only be deployed if it is current
   CONSTRAINT [CK dbo.Revisions Only Deployed If Current]
       CHECK (IsDeployed = 'false' OR 
           (CurrentRevisionID = RevisionID AND IsDeployed = 'true')),

   -- For denormalization via FK to dbo.PublicNames
   CONSTRAINT [UQ dbo.Revisions RevisionID, CurrentRevisionID] 
       UNIQUE NONCLUSTERED (RevisionID, CurrentRevisionID),

   -- Unique current revision per config
   INDEX [UQ dbo.Revisions One Current Revision Per Config]
       UNIQUE (ConfigID, IsCurrent)
       INCLUDE (CurrentRevisionID)
       WHERE CurrentRevisionID != 0
);

公共名稱

CREATE TABLE dbo.PublicNames (
   PublicNameID integer NOT NULL,
   RevisionID integer NOT NULL,
   CurrentRevisionID integer NOT NULL
       DEFAULT 0,
   PublicName varchar(100) NOT NULL,

   CONSTRAINT [PK dbo.PublicNames PublicNameID]
       PRIMARY KEY CLUSTERED (PublicNameID),

   CONSTRAINT [FK dbo.PublicNames -&gt; dbo.Revisions RevisionID]
       FOREIGN KEY (RevisionID) 
           REFERENCES dbo.Revisions (RevisionID),

   -- Denormalized, kept in sync via cascade
   CONSTRAINT [FK dbo.PublicNames -&gt; dbo.Revisions RevisionID, CurrentRevisionID]
       FOREIGN KEY (RevisionID, CurrentRevisionID)
       REFERENCES dbo.Revisions (RevisionID, CurrentRevisionID)
       ON UPDATE CASCADE,

   -- Public names unique within a revision
   CONSTRAINT [UQ dbo.PublicNames PublicName, RevisionID]
       UNIQUE NONCLUSTERED (PublicName, RevisionID),

   -- To support foreign key
   INDEX [IX dbo.PublicNames RevisionID, CurrentRevisionID]
       NONCLUSTERED (RevisionID, CurrentRevisionID),

   -- Public names unique across all current revisions
   INDEX [UQ dbo.PublicNames Unique Current Public Names]
       UNIQUE NONCLUSTERED (PublicName)
       INCLUDE (CurrentRevisionID)
       WHERE CurrentRevisionID != 0
);

數據

問題中提供的範例數據和狀態：

INSERT INTO dbo.Configs (ConfigID, ConfigName) VALUES
   (17, 'config_foo'),
   (42, 'config_bar');
 
INSERT INTO dbo.Revisions (RevisionID, ConfigID, CreatedAt, RevisionDescription, Foo, Bar) VALUES
   (11, 17, '2021-05-29 09:07:18', 'Foo configuration, first draft', 81, 'true'),
   (19, 17, '2021-05-29 10:42:17', 'Foo configuration, second draft', 73, 'true'),
   (23, 42, '2021-05-29 09:36:52', 'Bar configuration, first draft', 118, 'false');

INSERT INTO dbo.PublicNames (PublicNameID, RevisionID, PublicName) VALUES
 -- public names for foo configuration, first draft
 (83, 11, 'some.name'),
 (84, 11, 'other.name'),
 -- public names for foo configuration, second draft
 (85, 19, 'revised.name'),
 (86, 19, 'other.name'),
 (87, 19, 'third.name'),
 -- public names for bar configuration, first draft;
 -- some of the names here are the same used by foo configurations
 (88, 23, 'some.name'),
 (89, 23, 'unique.name'),
 (90, 23, 'other.name');
 
-- Foo configuration has a current, published revision:
UPDATE dbo.Revisions SET 
   CurrentRevisionID = 19, 
   IsDeployed = 'true' 
WHERE ConfigID = 17 
AND RevisionID = 19;

詢問

狀態查詢：

WITH 
   NumberedRevisions AS
   (
       SELECT 
           R.*,
           Revision =
               ROW_NUMBER() OVER (
                   PARTITION BY R.ConfigID
                   ORDER BY R.CreatedAt, R.RevisionID)
       FROM dbo.Revisions AS R
   )
SELECT
   C.ConfigName,
   R.Revision,
   R.IsDeployed,
   R.IsCurrent,
   Names =
       STRING_AGG(P.PublicName, ',')
           WITHIN GROUP (
               ORDER BY P.PublicName)
FROM dbo.Configs AS C
JOIN NumberedRevisions AS R
   ON R.ConfigID = C.ConfigID
JOIN dbo.PublicNames AS P
   ON P.RevisionID = R.RevisionID
GROUP BY
   C.ConfigID,
   C.ConfigName, 
   R.Revision, 
   R.IsDeployed, 
   R.IsCurrent
ORDER BY
   C.ConfigID,
   Revision;

測試

嘗試設置第三行電流：

UPDATE dbo.Revisions SET
   CurrentRevisionID = 23
WHERE ConfigID = 42
AND RevisionID = 23;

錯誤：

Msg 2601, Level 14, State 1
Cannot insert duplicate key row in object 'dbo.PublicNames' with unique index 
'UQ dbo.PublicNames Unique Current Public Names'. 
The duplicate key value is (other.name).
The statement has been terminated.

db<>小提琴展示

以上是允許引擎強制非規範化的相當標準的技術，因此我們可以使用約束和過濾索引來強制數據完整性——不管並發性或其他問題（產品錯誤除外）。有關更多資訊，請參閱Alexander Kuznetsov 的非規範化以強制執行業務規則。

通常會有應用程序方面的影響，但在實踐中這些影響通常會令人驚訝地易於管理。在 SQL Server 中，這通常是通過將任何額外的實現複雜性抽像到一個模組（通常是儲存過程或函式）中來完成的。這保留了乾淨和一致的 API。

一個人無法打敗您獲得的良好睡眠，因為知道數據庫始終保持一致是安全的。

引用自：https://dba.stackexchange.com/questions/292567

當限制為一對多時，在一對多對多的橋接關係中實施唯一性

配置

修訂

公共名稱

數據

詢問

測試

相關問答

僅當 Y 值相同時才允許多行具有相同的 X 值？

簡化重複的 INSERT 命令，為未找到的值放置 NULL

如何約束一個表中的行值以匹配另一表中的行值？

PostgreSQL 觸發更新函式鎖機制

更新觸發器中的“函式不重音（文本）不存在”

如何為其他一些列的唯一組合只強制一個 NULL 值？