表中的唯一 ID 集

我想代表任意數量的化學品的混合物。由於這裡的化學品和混合物之間的關係是多對多的，我想我會像這樣實現它（簡化）：

CREATE TABLE chemicals (
   name text PRIMARY KEY,
   chem_id SERIAL UNIQUE NOT NULL
);
CREATE TABLE mixtures (
   mixture_id SERIAL PRIMARY KEY,
);
CREATE TABLE mixture_chems (
   mixture_id INTEGER REFERENCES mixtures (mixture_id),
   chem_id INTEGER REFERENCES chemicals (chem_id)
);

但我也想強制執行任何特定組合（通過表中的行）mixture_id引用的只有一個（唯一）。chem_id``mixture_chems

我如何在 PostgreSQL 中實現它？

有人建議我可能需要使用觸發器來計算一些新值，以唯一標識混合物，然後對其強制唯一性。關於如何實施的想法，或者在這裡是否合適？

我同意那個人的看法。這是一個實現。

基本上，將所有s的UNIQUE數組列添加到表中，並使其與觸發器保持同步。數組必須一致地排序，我為此使用了額外的模組**intarray**並優化了性能。chem_id``mixtures

由於缺乏定義，我假設頻繁的多行寫入，使其成為Postgres 10 引入的轉換錶的完美候選者。請參閱：

• Postgres 中的“轉換錶”是什麼？

重要提示：從技術上講，這在 Postgres 10 中有效。但是在測試時我遇到了一個看起來很熟悉的 intarray 函式錯誤：由於不正確的內部數組尺寸，空數組不會比較相等。Tom Lane 為 Postgres 11 找到並修復了這個問題，但它沒有向後移植到 Postgres 10。我強烈建議使用Postgres 11。

原來是我之前報告自己的另一個錯誤實例。見這里和這裡。我花了一段時間來重現並獲得完整的畫面。

這使用了各種高級功能。不建議初學者使用。

程式碼

CREATE TABLE chemicals (
 chem_id serial UNIQUE NOT NULL
, name text PRIMARY KEY
);

CREATE TABLE mixtures (
 mixture_id serial PRIMARY KEY
**, chem_ids int[] UNIQUE**  -- default NULL !
);

CREATE TABLE mixture_chems (
 mixture_id int REFERENCES mixtures (mixture_id)
, chem_id int    REFERENCES chemicals (chem_id)
);

INSERT扳機

CREATE OR REPLACE FUNCTION trg_mixture_chems_insaft()
 RETURNS trigger AS
$func$
BEGIN
  UPDATE mixtures AS m
  SET    chem_ids = sort(COALESCE(m.chem_ids, '{}') + n.chem_ids)
  FROM  (
     SELECT mixture_id, array_agg(chem_id) AS chem_ids
     FROM   new_table
     GROUP  BY 1
     ) n
  WHERE m.mixture_id = n.mixture_id;

  RETURN NULL;
END
$func$  LANGUAGE plpgsql;


CREATE TRIGGER mixture_chems_insaft
AFTER INSERT ON mixture_chems
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_insaft();

UPDATE扳機

CREATE OR REPLACE FUNCTION trg_mixture_chems_upaft()
 RETURNS trigger AS
$func$
BEGIN
  UPDATE mixtures AS m
  SET    chem_ids = sort(COALESCE(m.chem_ids, '{}')
                       - COALESCE(o.chem_ids, '{}')
                       + COALESCE(n.chem_ids, '{}'))
  FROM  (
     SELECT mixture_id, array_agg(chem_id) AS chem_ids
     FROM   new_table
     GROUP  BY 1
     ) n
  FULL  JOIN (
     SELECT mixture_id, array_agg(chem_id) AS chem_ids
     FROM   old_table
     GROUP  BY 1
     ) o USING (mixture_id)
  WHERE m.mixture_id = COALESCE(n.mixture_id, o.mixture_id)
  AND   m.chem_ids IS DISTINCT FROM sort(COALESCE(m.chem_ids, '{}')
                                       - COALESCE(o.chem_ids, '{}')
                                       + COALESCE(n.chem_ids, '{}'));

  RETURN NULL;
END
$func$  LANGUAGE plpgsql;


CREATE TRIGGER mixture_chems_upaft
AFTER UPDATE ON mixture_chems
REFERENCING NEW TABLE AS new_table
           OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_upaft();

DELETE扳機

CREATE OR REPLACE FUNCTION trg_mixture_chems_delaft()
 RETURNS trigger AS
$func$
BEGIN
  UPDATE mixtures AS m
  SET    chem_ids = m.chem_ids - o.chem_ids  -- assuming this does not upset sort order!
  FROM  (
     SELECT mixture_id, array_agg(chem_id) AS chem_ids
     FROM   old_table
     GROUP  BY 1
     ) o
  WHERE m.mixture_id = o.mixture_id
  AND   m.chem_ids IS DISTINCT FROM (m.chem_ids - o.chem_ids);

  RETURN NULL;
END
$func$  LANGUAGE plpgsql;


CREATE TRIGGER mixture_chems_delaft
AFTER DELETE ON mixture_chems
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_delaft();

db<>在這裡擺弄

這種實施是嚴格的：沒有化學物質的混合物 ( chem_ids = '{}') 只是另一種只允許一次的情況。您可能希望多次允許這樣做。只有在刪除所有現有組件後才能達到此狀態，新插入的行mixtures開始chem_ids IS NULL以避開此UNIQUE約束。

並且您可能想要添加一個PRIMARY KEY約束以禁止多次將相同的化學品添加到混合物中：

CREATE TABLE mixture_chems (
 mixture_id INTEGER REFERENCES mixtures (mixture_id)
, chem_id INTEGER REFERENCES chemicals (chem_id)
**, PRIMARY KEY (mixture_id, chem_id)**
);

但我的實現不管怎樣都有效。

有關的：

• PostgreSQL 可以對數組元素有唯一性約束嗎？
• 如何在 PostgreSQL 中實現多對多關係？

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