کلید اصلی و خارجی: ON DELETE، ایندکس FK، DEFERRABLE و NOT VALID

روابط را به دیتابیس بسپارید، نه به برنامه

هر برنامه‌ای یک روز باگ دارد، یک اسکریپت دستی روی سرور اجرا می‌شود یا یک سرویس دوم به همان دیتابیس وصل می‌شود. کلید خارجی (FOREIGN KEY) تضمین می‌کند سفارشی بدون مشتری یا ردیف سفارشی بدون سفارش هیچ‌وقت وجود نداشته باشد، مهم نیست داده از کجا آمده باشد.

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  qty      int NOT NULL CHECK (qty > 0)
);
CREATE INDEX ON orders (customer_id);
CREATE INDEX ON order_items (order_id);

ایندکس ستون FK خودکار نیست

پستگرس برای ستون مرجع (مثل orders.customer_id) ایندکس نمی‌سازد. بدون آن، حذف یا تغییر هر مشتری یک Seq Scan کامل روی orders اجرا می‌کند و JOINها هم کند می‌شوند. این کوئری FKهای بدون ایندکس (ستون اول) را پیدا می‌کند:

SELECT c.conrelid::regclass AS tbl, c.conname, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (SELECT 1 FROM pg_index i
                  WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]);

DEFERRABLE: بررسی در پایان تراکنش

به‌طور پیش‌فرض قید بعد از هر دستور بررسی می‌شود. گاهی لازم است وضعیت موقتاً نامعتبر باشد؛ مثلاً جابه‌جا کردن ترتیب دو نقشه در کاتالوگ که روی (catalog_id, position) قید UNIQUE دارد:

ALTER TABLE catalog_items
  ADD CONSTRAINT uq_position UNIQUE (catalog_id, position) DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS uq_position DEFERRED;
UPDATE catalog_items SET position = 2 WHERE id = 10;
UPDATE catalog_items SET position = 1 WHERE id = 11;
COMMIT;   -- بررسی همین‌جا انجام می‌شود

افزودن FK به جدول بزرگ بدون توقف

ALTER TABLE order_items ADD CONSTRAINT fk_design
  FOREIGN KEY (design_code) REFERENCES designs (code) NOT VALID;   -- فوری
ALTER TABLE order_items VALIDATE CONSTRAINT fk_design;             -- بدون قفل سنگین نوشتن

نکته‌هایی که کمتر کسی می‌داند

• هر درج در جدول فرزند روی ردیف والد قفل FOR KEY SHARE می‌گیرد؛ اگر هزاران درج هم‌زمان به یک والد اشاره کنند (مثلاً «مشتری نقدی» پیش‌فرض)، روی آن ردیف رقابت قفل ایجاد می‌شود.
• قید UNIQUE یا PK که DEFERRABLE باشد نمی‌تواند هدف ON CONFLICT باشد؛ اگر upsert لازم دارید، آن قید را immediate نگه دارید.
• از نسخه‌ی 15 می‌توانید فقط بخشی از ستون‌های FK مرکب را NULL کنید: ON DELETE SET NULL (assignee_id) بدون دست زدن به tenant_id.
• ON DELETE CASCADE زنجیره‌ای می‌تواند با یک DELETE کوچک هزاران ردیف را در چند جدول پاک کند؛ روی جدول‌های مالی به‌جای آن soft delete یا RESTRICT را ترجیح دهید.
• pg_dump داده را اول و قیدها را آخر بازمی‌گرداند؛ پس ترتیب درج جدول‌ها هنگام restore مشکل FK نمی‌سازد، اما --data-only چنین مزیتی ندارد و --disable-triggers لازم می‌شود.