فصل ۳: طراحی و قیدها — داده‌ای که نمی‌شود خرابش کرد

UNIQUE، CHECK، domain و ستون‌های generated

قاعده‌های کسب‌وکار را در خود جدول بنویسید

UNIQUE و NULL

در SQL دو NULL با هم برابر نیستند؛ پس UNIQUE اجازه می‌دهد چند ردیف NULL داشته باشید. گاهی همین را می‌خواهیم (مشتری‌هایی که ایمیل ندارند) و گاهی نه. از نسخه‌ی 15:

CREATE TABLE price_list (
  design_code text NOT NULL,
  size_code   text NOT NULL,
  dealer_id   int,                 -- NULL یعنی قیمت عمومی
  price_rial  bigint NOT NULL CHECK (price_rial > 0),
  UNIQUE NULLS NOT DISTINCT (design_code, size_code, dealer_id)
);

-- یکتایی فقط بین رکوردهای فعال (soft delete)
CREATE UNIQUE INDEX uq_customer_mobile_active
  ON customers (mobile) WHERE deleted_at IS NULL;

CHECK

CHECK هر عبارتی روی ستون‌های همان ردیف را می‌پذیرد:

ALTER TABLE orders
  ADD CONSTRAINT chk_delivery CHECK (delivered_at IS NULL OR delivered_at >= created_at),
  ADD CONSTRAINT chk_discount CHECK (discount_pct BETWEEN 0 AND 30);

domain: نوع داده با قاعده

domain یک نوع پایه به‌علاوه‌ی قید است که یک بار تعریف و همه‌جا استفاده می‌شود. مثال بومی: موبایل و کد ملی با الگوریتم رقم کنترل.

CREATE DOMAIN mobile_ir AS text CHECK (VALUE ~ '^09[0-9]{9}$');

CREATE FUNCTION is_valid_melli_code(c text) RETURNS boolean
LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT CASE WHEN c !~ '^[0-9]{10}$' OR c ~ '^(\d)\1{9}$' THEN false
  ELSE (SELECT CASE WHEN r < 2 THEN r ELSE 11 - r END = substr(c, 10, 1)::int
        FROM (SELECT sum(substr(c, i, 1)::int * (11 - i)) % 11 AS r
              FROM generate_series(1, 9) AS i) s)
  END
$$;

CREATE DOMAIN melli_code AS text CHECK (is_valid_melli_code(VALUE));

CREATE TABLE weavers (
  id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   text NOT NULL,
  mobile mobile_ir,
  code   melli_code UNIQUE
);

ستون‌های generated

ستونی که همیشه از ستون‌های دیگر حساب می‌شود و دستی قابل نوشتن نیست؛ مساحت فرش یا جمع ردیف فاکتور:

ALTER TABLE order_items
  ADD COLUMN width_cm  int NOT NULL DEFAULT 300,
  ADD COLUMN length_cm int NOT NULL DEFAULT 400,
  ADD COLUMN area_m2 numeric(6,2) GENERATED ALWAYS AS (width_cm * length_cm / 10000.0) STORED;

در نسخه‌های 16 و 17 فقط STORED داریم (روی دیسک ذخیره می‌شود و قابل ایندکس است). نسخه‌ی 18 ستون VIRTUAL را اضافه کرده و آن را پیش‌فرض کرده است؛ هنگام خواندن محاسبه می‌شود.

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

  • CHECK با نتیجه‌ی NULL قبول می‌شود؛ CHECK (discount_pct <= 30) ردیف با discount_pct خالی را رد نمی‌کند. اگر مقدار الزامی است NOT NULL را جدا بگذارید.
  • عبارت ستون generated و ایندکس عبارتی باید IMMUTABLE باشد و پستگرس آن را بررسی می‌کند؛ در CHECK بررسی نمی‌شود، اما قاعده همان است: CHECK با now() یا ::date روی timestamptz فقط لحظه‌ی درج را می‌سنجد و بعد از restore ممکن است شکست بخورد. اگر تابعی را به دروغ IMMUTABLE اعلام کنید، ایندکس‌ها بی‌صدا نادرست می‌شوند.
  • UNIQUE و PRIMARY KEY خودشان ایندکس می‌سازند؛ ایندکس دستی روی همان ستون فقط نوشتن را کند و فضا را دوبرابر می‌کند. در \d جدول، ایندکس‌های تکراری را جست‌وجو کنید.
  • ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) NOT VALID و سپس VALIDATE، مثل FK، اجازه می‌دهد روی جدول بزرگ بدون قفل طولانی قید اضافه کنید.
  • domainی که NOT NULL دارد باز هم در خروجی LEFT JOIN یا ستون تازه اضافه‌شده NULL می‌گیرد؛ مستندات خود پستگرس توصیه می‌کند NOT NULL را روی ستون بگذارید نه روی domain.
درس قبلی درس بعدی

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.