約束到邊界的非重疊矩形

我正在嘗試模擬電路板上零件的放置。沒有任何有意義的約束，我的基本架構如下所示：

create table part (
   part_id bigserial primary key,
   name text not null,
   width double precision not null,
   height double precision not null
);
create table board (
   board_id bigserial primary key,
   width double precision not null,
   height double precision not null
);
create table board_part (
   board_id bigint not null references board,
   part_id bigint not null references part,
   position point not null
);

（SQL 小提琴，視覺化）

對於b和b2任何board_parts，我想強制執行以下約束：

1. b躺在板上：

box(b.position, point(b.part.width,b.part.height))
   <@ box(point(0,0), point(b.board.width,b.board.height))

2. b如果它們位於同一塊板上，b2則不要重疊：

b.board_id != b2.board_id or
not (box(b.position, point(b.part.width,b.part.height))
       && box(b2.position, point(b2.part.width,b2.part.height)))

**我怎樣才能做到這一點（沒有太多的數據重複）？**更改架構很好。

這是我最好的嘗試（SQL Fiddle），靈感來自 Erwin對我之前的問題的回答。它強制執行我想要的約束，但表中有很多重複數據board_part。我想我可以編寫一個函式來自動填充board_width、board_height、part_width和part_height欄位，但是周圍有這麼多重複數據仍然感覺不對。此外，鍵入width/height欄位感覺就像是 hack。

基本答案

我建議使用幾何類型**box**和排除約束（Postgres 9.2+）。應該是您問題的完美解決方案。它隱式地創建一個也支持某些查詢的 GiST 索引。

將它與相等性結合起來board_id以允許在一個表中使用多個板。您將需要額外的模組btree_gist。每個數據庫一次：

CREATE EXTENSION btree_gist;

要將零件限制在板上，請添加CHECK約束。您需要桌子上的板的限制框board_part（冗餘）。可能看起來像這樣：

CREATE TABLE board_part (
 board_id bigint NOT NULL REFERENCES board
, board_box box NOT NULL
, part_id bigint NOT NULL REFERENCES part
, part_box box NOT NULL
, EXCLUDE USING gist (board_id WITH =, part_box WITH &&)
, CHECK (part_box <@ board_box)
);

&&..“重疊”運算符

<@..“包含”運算符

缺點：您需要board_part冗餘表格中每個零件和框的尺寸。

高級答案

為了避免冗餘儲存，我更喜歡這個新想法：假IMMUTABLE函式返回一個 id 的盒子並在這些函式上建立約束。您的表格可以只使用原始設計的列。

我進行了完整的測試以驗證它是否有效。

完整架構

基表：

CREATE TABLE part (
 part_id serial PRIMARY KEY
, part text NOT NULL
, wide int NOT NULL
, high int NOT NULL
);

CREATE TABLE board (
 board_id serial PRIMARY KEY
, board text NOT NULL
, wide int NOT NULL
, high int NOT NULL
);

IMMUTABLE職能：

CREATE OR REPLACE FUNCTION f_boardbox(_board_id int)
 RETURNS box
 LANGUAGE sql IMMUTABLE AS
'SELECT box(point(0,0), point (b.wide, b.high))
FROM   public.board b WHERE board_id = $1'; 

CREATE OR REPLACE FUNCTION f_partbox(_part_id int, _wide int, _high int)
 RETURNS box
 LANGUAGE sql IMMUTABLE AS
'SELECT box(point($2,$3), point($2 + p.wide, $3 + p.high))
FROM   public.part p WHERE part_id = $1';

替換public為表的實際架構。

在 Postgres 9.6 或更高版本中添加PARALLEL SAFE. 看：

• 何時將函式標記為 PARALLEL RESTRICTED 與 PARALLEL SAFE？

主表：

CREATE TABLE board_part (
 board_id int NOT NULL REFERENCES board
, part_id int NOT NULL REFERENCES part
, wide int NOT NULL
, high int NOT NULL
, EXCLUDE USING gist (board_id WITH =, f_partbox(part_id, wide, high) WITH &&)
, CHECK (f_partbox(part_id, wide, high) <@ f_boardbox(board_id))
);

當然，如果您更新 a board_idor part_idin use 的維度，您會部分取消索引和/或約束。您需要根據IMMUTABLE函式的返回值永遠不會改變的承諾重新創建任何索引或約束。看：

• PostgreSQL 是否支持“不區分重音”排序規則？

但是，您可以使用觸發器來僅更新受影響的行來避免這種昂貴的操作：

觸發器

CREATE OR REPLACE FUNCTION f_board_upaft()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
BEGIN
  UPDATE board_part
  SET    board_id = board_id        -- enough to trigger CHECK constraint
  WHERE  board_id = NEW.board_id;   -- limit to relevant rows

  RETURN NULL;
END
$func$;

CREATE TRIGGER board_upaft
AFTER UPDATE OF wide, high ON board  -- limit to relevant columns
FOR EACH ROW EXECUTE PROCEDURE f_board_upaft();


CREATE OR REPLACE FUNCTION f_part_upaft()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
BEGIN
  UPDATE board_part
  SET    part_id = 0
  WHERE  part_id = NEW.part_id;

  UPDATE board_part
  SET    part_id = NEW.part_id
  WHERE  part_id = 0;               -- enough to update EXCL. constraint

  RETURN NULL;
END
$func$;

CREATE TRIGGER part_upaft
AFTER UPDATE OF wide, high ON part
FOR EACH ROW EXECUTE PROCEDURE f_part_upaft();

part_id = 0是表part中大小為 0 的特殊行。觸發器需要。

測試數據：

INSERT INTO board (board, wide, high)
VALUES ('b1010',10,10), ('b2030',20, 30);

INSERT INTO part (part_id, part, wide, high)
VALUES (0,'p0',0,0);          -- special row needed for trigger!

INSERT INTO part (part, wide, high)
VALUES ('p11',1,1), ('p33',3,3);

INSERT INTO board_part (board_id, part_id, wide, high)
VALUES (1,1,3,3)
    , (1,1,5,5)   -- no overlap, inside board
    , (2,1,3,3);  -- different board, no overlap

測試

如果觸發器和約束完成它們的工作，這些必須引發異常：

UPDATE part SET wide = 6 , high = 6
WHERE  part_id = 1;            -- violates EXCL. & CHECK constraint

UPDATE part SET wide = 3 , high = 3
WHERE  part_id = 1;            -- violates EXCL. constraint

UPDATE board SET wide = 2 , high = 2
WHERE  board_id = 1;            -- violates CHECK constraint

db<>fiddle here

舊sqlfiddle

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