強制每家公司至少有一位 CEO

我的要求是確保每家公司至少有一位 CEO。

具有要求db<>fiddle的範例數據庫模型。

基本上，如果不任命另一位 CEO，就不能將一位 CEO 從公司中撤職。當沒有其他 CEO 時，CEO 不能在公司中擔任其他角色。但是可以刪除所有 3 個實體：公司、員工、角色。

我嘗試了以下想法：

1. 使用before update or delete觸發器：查看圖表並嘗試猜測執行後會發生什麼。它不起作用，因為它會立即執行。
2. 使用after update or delete約束觸發器：如果也被刪除DEFERRABLE INITIALLY DEFERRED，如何檢查 CEO的數量？staff或者如何允許，如果公司與員工和角色一起被刪除？而且用pgtap進行測試並不容易。

還有什麼想法嗎？

我問的不是關於創造的問題，而是關於在不任命另一位 CEO 的情況下不能將 CEO 從公司中移除的限制。這只是觸發器和關係困難的最簡單範例。

---

找到了解決方法

見db<>fiddle。在核心中，以下解決方法似乎有效：

1. 定義after update or delete觸發那些檢查刪除後的狀態。但不要檢查相關實體是否已刪除。因為關係被忽略，所以除了根之外的整個鏈都需要觸發器。在和的範例roles中staff：

CREATE FUNCTION roles_updates() RETURNS trigger AS $$
BEGIN
 IF
   -- check only if staff wasn't deleted too, ignore otherwise
   (select count(1) from staff where name = OLD.staffid) > 0
   -- check if there is at least one ceo left
   and (
       select count(1)
       from roles r
       join staff s on r.staffid = s.name
       where
         s.company in (select company from staff where name = OLD.staffid)
         and r.role = 'ceo') < 1
 THEN
   RAISE EXCEPTION 'roles: company without ceo isn''t allowed';
 END IF;

 RETURN OLD;
END; $$ LANGUAGE plpgsql;

2. 定義DEFERRABLE INITIALLY DEFERRED關係。這允許在事務結束時檢查外鍵。因此我可以使用：

begin;
delete from company where name = 'amd';
delete from staff where name = 'lisa';
delete from roles where id = 1;
rollback;

而是將其放入一個語句/公用表表達式中，例如：

with DeletedCompany as (
 delete from company where name = 'amd'
),
DeletedStaff as (
 delete from staff where name = 'lisa'
)
delete from roles where id = 1;

仍然有一個非工作案例：

begin;
delete from roles where id = 1;
delete from staff where name = 'lisa';
delete from company where name = 'amd';
rollback;

我認為可以通過使用約束觸發器來修復它，該觸發器在事務結束時執行。但是因為很難測試，所以我更喜歡上面的解決方法。

編輯 15.06.21：正如@DanielVérité 指出的那樣，這只適用於沒有異常的序列化隔離模式。在文件和缺點中閱讀有關此主題的更多資訊，尤其是有關客戶端處理的資訊。

觸發器的主要問題是它忽略了並發更改。您在評論中請求了一個範例，所以這裡是一個簡單的範例。

從 dbfiddle 開始，’lisa’ 是 ‘amd’ 的 CEO，讓我們添加第二個 CEO：

插入員工價值觀（alice'，'amd'）；
插入角色值（2，'ceo'，'alice'）；

tst=> 從角色中選擇 *；
編號 | 角色 | 工作人員
----+------+---------
1 | 首席執行官 | 麗莎
2 | 首席執行官 | 愛麗絲

現在刪除兩個不同並發事務中的兩個 CEO：

TX1>開始；
開始

Tx1*> 從工作人員 ID='lisa' 的角色中刪除；
刪除 1

TX2>開始；
開始

Tx2*> 從角色中刪除 staffid='alice';
刪除 1

Tx2*> 送出；
犯罪

Tx1*> 送出；
犯罪

Tx1> 從角色中選擇 * ；
編號 | 角色 | 工作人員
----+------+---------
(0 行)

結果：沒有引發錯誤，並且“amd”沒有 CEO。

在觸發器中執行的檢查悄悄通過了，因為當 Tx2 執行觸發器時，就它而言，帶有 ’lisa’ 的角色仍然存在。

那麼如何避免呢？一般來說，您可以在對對像或行進行任何操作之前鎖定對像或行（悲觀鎖定防止並發），或者使用帶有重試邏輯的可序列化事務（樂觀鎖定）。但是觸發器不是這樣做的地方。

處理此問題的一種方法是創建一個始終指向其中一位 CEO的外鍵 from companyto ：roles

/* redundant, but needed as destination of the foreign key */
ALTER TABLE roles ADD UNIQUE (role, id);

/* also redundant */
ALTER TABLE company ADD ceo_role text DEFAULT 'ceo' NOT NULL;
ALTER TABLE company ADD CHECK (ceo_role = 'ceo');
ALTER TABLE company ADD ceo_id integer;
UPDATE company SET ceo_id = roles.id
  FROM staff JOIN roles ON roles.staffid = staff.name
  WHERE staff.company = company.name
    AND roles.role = 'ceo'
    /* only take the CEO with the least roles.id */
    AND NOT EXISTS (SELECT 1
                    FROM staff AS s2 JOIN roles AS r2 ON r2.staffid = d2.name
                    WHERE s2.company = company.name
                      AND r2.role = 'ceo'
                      AND r2.id < roles.id)

ALTER TABLE company ADD CONSTRAINT has_at_least_one_ceo
  FOREIGN KEY (ceo_role, ceo_id) REFERENCES roles (role, id)
  DEFERRABLE INITIALLY DEFERRED;

我將外鍵設置為可延遲的，這樣您就可以在一次交易中添加一家公司及其第一位 CEO——您只需先添加該公司即可。

該解決方案的缺點是

1. 冗餘數據
2. 罷免首席執行官所需的複雜程序

從好的方面來說，您不必擔心觸發函式中的並發性。

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