مدل‌سازی رابطه‌ای: PK/FK/UNIQUE/CHECK/NULL

۱. صفحه اول: دیتابیس می‌تواند رکورد را بپذیرد اما domain را نقض کند

فرض کنید یک فروشگاه کوچک سه جدول customers، products و orders دارد. برنامه برای هر رکورد ID می‌سازد، در فرم ایمیل را کنترل می‌کند، قیمت منفی را در UI نمی‌پذیرد و قبل از ثبت سفارش وجود مشتری را بررسی می‌کند. تا وقتی همه ورودی‌ها فقط از همین مسیر بیایند، سیستم سالم به نظر می‌رسد. اما یک import، اسکریپت یا سرویس دیگر می‌تواند این کنترل‌ها را دور بزند و دیتابیس را با ایمیل تکراری، قیمت منفی یا سفارشی با customer_id نامعتبر پر کند.

مسئله محدود این درس این است: کدام invariantهای domain باید در مدل رابطه‌ای enforce شوند و چطور ثابت می‌کنیم که دیتابیس واقعاً آن‌ها را enforce می‌کند؟ خروجی اصلی درس Relational Constraint Model Record v1 است. برای هر invariant مهم باید constraint پیشنهادی، نمونه مجاز، نمونه ردشونده، نتیجه مشاهده‌شده در دیتابیس، دلیل domain و قانون باقی‌مانده خارج از دیتابیس ثبت شود.

مجموعه مکانیزم‌ها عمداً کوچک است: PRIMARY KEY، FOREIGN KEY، UNIQUE، CHECK و NULL/NOT NULL. هدف حفظ کردن syntax نیست. هدف این است که هر keyword به یک invariant مشخص وصل شود و بعد با probe مثبت و منفی برای آن evidence ساخته شود. داشتن ERD زیبا یا یک CREATE TABLE بدون آزمون نقض، proof محسوب نمی‌شود.

۲. انتخاب مسیر: مسیر متفاوت، evidence یکسان

Foundation. ابتدا invariant را به زبان ساده بنویسید و بعد SQL را انتخاب کنید. مثال فروشگاه را قدم‌به‌قدم اجرا کنید و هر بار فقط یک نوع violation را بررسی کنید.

Repair. از defect شروع کنید. اگر ایمیل تکراری وارد شده است، کل schema را بازطراحی نکنید. پیش‌بینی کنید چه چیزی باید reject شود، کوچک‌ترین constraint مرتبط را اصلاح کنید، probe را دوباره اجرا کنید و evidence را نگه دارید.

Experienced. می‌توانید توضیح syntax پایه را کوتاه کنید، اما نمی‌توانید از تصمیم‌های اصلی عبور کنید: candidate key، منطق referential action، رفتار UNIQUE با NULL در PostgreSQL، مرز CHECK و NOT NULL، mutation evidence و transfer مستقل.

در هر سه مسیر خروجی و evidence bar یکسان است. AI می‌تواند بعد از بیان invariant گزینه‌های SQL پیشنهاد کند، اما اجازه ندارد نتیجه اجرای دیتابیس را بسازد یا تصمیم نهایی مدل‌سازی را به‌جای شما بگیرد.

۳. مدل ذهنی: invariant → ستون‌ها → constraint → probe → evidence

برای هر قانون این زنجیره را حفظ کنید:

1. Invariant را مشخص کنید. مثال: «هر سفارش باید به یک مشتری موجود تعلق داشته باشد.»
2. جدول و ستون مالک قانون را پیدا کنید. اینجا orders.customer_id به customers.id وصل است.
3. کوچک‌ترین مکانیزم دیتابیس را انتخاب کنید. در این مثال FOREIGN KEY.
4. Probe مجاز بنویسید. سفارش با مشتری موجود باید ثبت شود.
5. Probe نامعتبر بنویسید. سفارش با مشتری ناموجود باید reject شود.
6. Observation را از inference جدا کنید. متن خطای PostgreSQL observation است؛ نتیجه‌گیری «این رابطه برای schema فعلی enforce می‌شود» inference محدود است.
7. Residual rule را ثبت کنید. authorization، workflow یا سیاست درست حذف مشتری ممکن است همچنان خارج از این constraint باشد.

این ترتیب جلوی خطای رایج را می‌گیرد: اول keyword موردعلاقه را انتخاب کنیم و بعد برایش دلیل بسازیم.

۴. لایه پایه: پنج قرارداد با پنج مسئولیت متفاوت

Primary Key — هویت سطر

PRIMARY KEY مجموعه ستون انتخاب‌شده را unique و non-null می‌کند. PostgreSQL از primary key مرکب پشتیبانی می‌کند، اما هر جدول فقط یک primary-key constraint دارد. [PG18-CONSTRAINTS] تصمیم واقعی این نیست که «همیشه integer ID بسازیم»؛ تصمیم این است که هویت سطر چیست و چه کلیدی باید مرجع سایر رابطه‌ها شود. حتی اگر surrogate key داشته باشیم، ممکن است business identifier جداگانه به UNIQUE نیاز داشته باشد.

Foreign Key — یکپارچگی مرجع

FOREIGN KEY مقدار ستون مرجع را به یک key معتبر در جدول referenced محدود می‌کند. [PG18-CONSTRAINTS] این constraint orphan reference را می‌بندد، اما خودش تعیین نمی‌کند رفتار صحیح محصول هنگام delete/update چیست. RESTRICT، NO ACTION، CASCADE و گزینه‌های دیگر اثرهای domain متفاوت دارند؛ انتخاب آن‌ها باید دلیل داشته باشد.

UNIQUE — یکتایی candidate key

UNIQUE یک candidate key یا ترکیب ستون‌ها را حفاظت می‌کند. در PostgreSQL به‌صورت پیش‌فرض NULLها برای uniqueness distinct در نظر گرفته می‌شوند؛ بنابراین چند NULL می‌توانند هم‌زمان وجود داشته باشند. NULLS NOT DISTINCT این قرارداد را تغییر می‌دهد. [PG18-CONSTRAINTS] این رفتار را به همه دیتابیس‌ها تعمیم ندهید.

CHECK — قانون row-local

CHECK یک شرط Boolean روی همان row را کنترل می‌کند؛ مثلاً price > 0. در PostgreSQL نتیجه TRUE یا UNKNOWN پذیرفته می‌شود، بنابراین اگر price برابر NULL باشد، CHECK (price > 0) به‌تنهایی nullability را نمی‌بندد. [PG18-CREATE-TABLE] اگر قیمت باید حتماً وجود داشته باشد، NOT NULL قرارداد جداگانه است.

NULL / NOT NULL — اختیاری بودن واقعی

Nullability بخشی از domain است. NOT NULL یعنی مقدار باید وجود داشته باشد. اجازه NULL یعنی نبود مقدار یک state معتبر است، نه اینکه validation ناقص مانده باشد. tracking code قبل از ارسال سفارش نمونه خوبی از یک مقدار واقعاً optional است.

۵. توضیح عمیق: همه constraintها یک نوع correctness را ثابت نمی‌کنند

Primary key فقط درباره identity همان key evidence می‌دهد. وجود آن به‌تنهایی uniqueness ایمیل، معتبر بودن قیمت یا وجود customer برای order را اثبات نمی‌کند. هر invariant باید constraint متناسب خودش را داشته باشد.

Foreign key یک رابطه مشخص را enforce می‌کند، اما authorization یا وضعیت business entity را ثابت نمی‌کند. همچنین نمی‌گوید حذف parent باید cascade شود یا متوقف بماند. اگر ON DELETE CASCADE انتخاب می‌کنید باید از domain آمده باشد، نه از راحتی syntax.

UNIQUE در حضور nullable value نیاز به تصمیم روشن دارد. فرض کنید orders.tracking_code optional است اما اگر مقدار داشت باید فقط متعلق به یک order باشد. رفتار پیش‌فرض PostgreSQL دقیقاً این قرارداد را می‌دهد: چند NULL مجاز است، ولی یک مقدار non-null تکراری reject می‌شود. اگر domain می‌گوید فقط یک NULL هم مجاز است، قرارداد دیگری لازم است. اصل مهم این است که null semantics پنهان نماند.

CHECK هم به‌سادگی overclaim می‌شود. CHECK (price > 0) مقدار منفی یا صفر non-null را رد می‌کند، ولی NULL ممکن است expression را UNKNOWN کند و accepted شود. پس برای «قیمت اجباری و مثبت» به هر دو NOT NULL و CHECK نیاز داریم. [PG18-CREATE-TABLE]

این درس از CHECK برای invariantهای cross-row یا cross-table استفاده نمی‌کند. مستندات PostgreSQL نیز این نوع استفاده را مرز امن عمومی CHECK نمی‌دانند. migration، concurrency، transaction isolation و performance خارج از scope این درس هستند. [PG18-CONSTRAINTS]

۶. مثال کامل: customers، products و orders

قوانین domain را قبل از SQL بنویسید:

• هویت customer برابر customers.id است؛
• email اجباری و unique است؛
• هویت product برابر products.id است؛
• SKU اجباری و unique است؛
• price اجباری و بزرگ‌تر از صفر است؛
• هر order باید customer موجود داشته باشد؛
• tracking code اختیاری است، اما هر مقدار non-null آن باید unique باشد.

یک مدل محدود PostgreSQL می‌تواند چنین باشد:

CREATE TABLE customers (
    id bigint PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text NOT NULL
);

CREATE TABLE products (
    id bigint PRIMARY KEY,
    sku text NOT NULL UNIQUE,
    price numeric(12,2) NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
    id bigint PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    tracking_code text UNIQUE
);

بعد باید آن را probe کنید. customer و product معتبر بسازید. یک order معتبر ثبت کنید. سپس email تکراری، price منفی، price برابر NULL و customer_id ناموجود را امتحان کنید. هر failure نوع متفاوتی از evidence است. بعد دو order با tracking_code = NULL بسازید و رفتار پیش‌فرض PostgreSQL را مشاهده کنید؛ سپس یک tracking code non-null را دوبار وارد کنید و rejection را ثبت کنید.

در evidence record ننویسید «constraintها درست کار کردند». دقیقاً بنویسید کدام probe اجرا شد، نتیجه دیتابیس چه بود، آن observation چه claim محدودی را پشتیبانی می‌کند و چه چیزی را ثابت نمی‌کند.

۷. failure modeهای مهم

«Primary key داریم، پس schema درست است.» خیر. PK فقط identity انتخاب‌شده را enforce می‌کند و جلوی duplicate email یا impossible price را به‌تنهایی نمی‌گیرد.

«Foreign key خودش سیاست delete را مشخص می‌کند.» خیر. رابطه و referential action دو تصمیم جدا هستند.

«UNIQUE با NULL در همه SQLها یک رفتار دارد.» خیر. رفتاری را که واقعاً در PostgreSQL استفاده می‌کنید ثبت کنید و portability boundary را بنویسید.

«CHECK(price > 0) یعنی price اجباری است.» خیر. NULL می‌تواند نتیجه UNKNOWN ایجاد کند؛ NOT NULL قرارداد جداست. [PG18-CREATE-TABLE]

«Constraint دیتابیس همه validationهای برنامه را حذف می‌کند.» خیر. authorization، workflow، پیام خطای مناسب برای کاربر و بعضی business ruleها همچنان خارج از constraint هستند.

۸. پل مسیر Experienced: candidate key و NULL را آگاهانه مدل کنید

سه تصمیم باید صریح باشد.

اول، row identity را از business identity جدا کنید. ممکن است id primary key باشد اما email یا sku همچنان candidate key باشند و UNIQUE بخواهند.

دوم، referential action را از روی domain انتخاب کنید. برای orderهای تاریخی شاید ON DELETE RESTRICT منطقی باشد؛ در domain دیگر شاید انتخاب دیگری درست باشد. این درس دنبال default جهانی نیست، دنبال rationale است.

سوم، قرارداد nullable uniqueness را جمله‌بندی کنید. «tracking code اختیاری است ولی اگر وجود داشت unique است» با «فقط یک row می‌تواند tracking code نداشته باشد» یکی نیست.

این تمرین را به indexing، migration، query planner یا concurrency گسترش ندهید. آن‌ها evidence متفاوتی لازم دارند.

۹. alternatives و tradeoffها

Surrogate PK + UNIQUE business key اغلب reference پایدار و uniqueness business را هم‌زمان نگه می‌دارد. Natural PK وقتی مناسب است که business identifier واقعاً پایدار و هویت‌ساز باشد. هیچ‌کدام به‌تنهایی بقیه مدل را اثبات نمی‌کنند.

Foreign key می‌تواند به primary key یا key unique مناسب اشاره کند، اما «از نظر SQL معتبر بودن» با «از نظر domain رابطه درست بودن» یکی نیست.

برای یک مقدار عددی اجباری، NOT NULL و CHECK دو بخش متفاوت rule را enforce می‌کنند: وجود مقدار و محدوده مقدار. برای داده optional، افزودن NOT NULL فقط برای ساده شدن code می‌تواند domain را اشتباه نمایش دهد.

معیار تصمیم این است: آیا invariant را می‌توانید در یک جمله بگویید، constraint مربوط را نشان دهید و یک case مجاز و یک case rejectشونده ارائه کنید که مستقیم همان invariant را probe کنند؟

۱۰. تمرین هدایت‌شده و مستقل: DAT001-LAB1

Starter عمداً constraintهای اصلی را ندارد و نباید skill را از قبل حل کند.

1. model-contract.json و starter schema را بخوانید.
2. قبل از SQL، invariant matrix را پر کنید.
3. solution.sql خودتان را در workspace learner بسازید.
4. probeهای مثبت و منفی را روی PostgreSQL محلی اجرا کنید.
5. نتیجه واقعی DB را در Relational Constraint Model Record v1 ثبت کنید.
6. قبل از mutation، failure مورد انتظار را پیش‌بینی کنید.
7. فقط invariant شکسته را repair و دوباره اجرا کنید.
8. transfer مربوط به support-ticket domain را مستقل انجام دهید.

بررسی‌های starter فقط سالم بودن تمرین را می‌سنجند و schema تعمیرشده را در اختیار شما نمی‌گذارند. solution.sql را خودتان بسازید و فقط observationهای واقعی PostgreSQL خودتان را به‌عنوان evidence ثبت کنید.

۱۱. verification و evidence: تعریف Done

برای هر critical dimension این ستون‌ها باید پر شود:

• Invariant — قانون domain؛
• Candidate Constraint — مکانیزم دقیق دیتابیس؛
• Allowed Example — عملیات یا row مجاز؛
• Rejected Example — عملیات یا row نامعتبر؛
• Observed DB Result — نتیجه واقعی PostgreSQL؛
• Domain Rationale — دلیل مالکیت rule توسط دیتابیس؛
• Residual Rule — rule باقی‌مانده خارج از دیتابیس.

حداقل یک mutation باید قبل از اجرا پیش‌بینی شود، اجرا شود، تشخیص داده شود، repair شود و rerun داشته باشد. یک insert موفق absence of invalid state را ثابت نمی‌کند؛ negative evidence بخش اصلی proof است.

۱۲. assessment و transfer: domain تیکت پشتیبانی

در domain جدید، organizationها member و support ticket دارند. membership باید به organization و user موجود وصل باشد. subject تیکت اجباری است. external reference اختیاری است اما اگر مقدار داشت باید unique باشد. permission بستن ticket همچنان authorization rule برنامه است.

بدون کپی SQL فروشگاه، row keyها و candidate keyها را انتخاب کنید، foreign keyها را مشخص کنید، nullability و row-local checkها را تعیین کنید و ruleهای خارج از دیتابیس را بنویسید. برای هر invariant یک probe مجاز و یک probe rejectشونده ثبت کنید. نتیجه نهایی باید observation را از inference جدا کند و یک residual limitation داشته باشد.

۱۳. منابع و تازگی

• [PG18-CONSTRAINTS] مستندات رسمی PostgreSQL 18، بخش 5.5 Constraints؛ بازبینی‌شده در 2026-09-14؛ موعد بازبینی 2026-12-31.
• [PG18-CREATE-TABLE] مستندات رسمی PostgreSQL 18، صفحه CREATE TABLE؛ بازبینی‌شده در 2026-09-14؛ موعد بازبینی 2026-10-17.

رفتارهای version-specific فقط در مرز PostgreSQL 18/current مستند شده‌اند. با تغییر مستندات، رسیدن review_due یا ناسازگار شدن lab با رفتار جاری PostgreSQL باید re-review انجام شود.