PostgreSQL DDL و طراحی Constraint-first Schema

۱. صفحه اول: ممکن است DDL بدون خطای syntax اجرا شود اما قرارداد داده هنوز ناقص باشد

فرض کنید جدول‌های customers و orders از قبل وجود دارند. تغییر جدید باید برای سفارش یک مرجع خارجی، مبلغ مثبت، وضعیت پیش‌فرض و ارتباط با مشتری اضافه کند. اگر کار را از «شکل جدول» شروع کنیم، معمولاً چند ALTER TABLE می‌نویسیم تا ستون‌ها و constraintها اضافه شوند؛ اما ممکن است ندانیم هر دستور دقیقاً کدام invariant را حفاظت می‌کند، با داده‌های قدیمی چه می‌کند و چه چیزی را درباره‌ی اجرای production اصلاً نمی‌توانیم نتیجه بگیریم.

این درس از مسیر مقابل می‌رود: اول invariant، بعد DDL. خروجی شما Constraint-First PostgreSQL DDL Record v1 است. برای هر invariant باید statement دقیق، نام constraint در جایی که مکانیزم اجازه می‌دهد، اثر مورد انتظار بر داده‌های موجود، probe مثبت، probe منفی، نتیجه‌ی مشاهده‌شده، مرجع فنی و یک محدودیت عملیاتی را ثبت کنید.

مرز درس روشن است: اینجا «migration production» طراحی نمی‌کنیم. sequencing کامل rollout، rollback، zero-downtime، concurrency و performance در scope این درس نیستند. هدف این است که مدل invariant محدودشده را به PostgreSQL DDL قابل‌مرور و evidence قابل‌بازبینی تبدیل کنیم.

۲. مسیر یادگیری: scaffold فرق می‌کند، معیار evidence نه

مسیر Foundation. ابتدا invariant worksheet را پر کنید. قبل از SQL مشخص کنید قانون مربوط به کدام ستون‌هاست، چه constraintی لازم دارد، نامش چیست و داده‌های قبلی باید چه وضعیتی داشته باشند.

مسیر Repair. از یک شکست مشخص شروع کنید. مثلاً اگر DEFAULT 'pending' به‌اشتباه به‌عنوان constraint وضعیت استفاده شده، فقط همان خطا را جدا کنید، یک مقدار نامعتبر پیش‌بینی کنید، constraint واقعی را اضافه کنید و probe را دوباره اجرا کنید.

مسیر Experienced. مرور syntax را کوتاه کنید اما evidence را حذف نکنید. named constraint، تفاوت validation مستقیم با staged validation، مرز دقیق NOT VALID در PostgreSQL 18، mutation evidence و residual limitation همچنان اجباری‌اند.

در همه‌ی مسیرها artifact نهایی یکی است. AI می‌تواند بعد از بیان invariant چند statement پیشنهادی بدهد، اما حق ندارد نتیجه‌ی اجرا را جعل کند یا تصمیم نهایی درباره‌ی مالکیت invariant را به‌جای شما بگیرد.

۳. مدل ذهنی: invariant → DDL → نام → مسیر داده موجود → probe → evidence

برای Constraint-first DDL ترتیب تصمیم‌ها چنین است:

1. Invariant را با زبان domain بنویسید. مثال: «مبلغ هر سفارش باید موجود و بزرگ‌تر از صفر باشد.»
2. مکانیزم دیتابیس را انتخاب کنید. required بودن با NOT NULL و مثبت بودن با CHECK (amount > 0) بیان می‌شود.
3. بین CREATE TABLE و ALTER TABLE بر اساس وضعیت واقعی ساختار تصمیم بگیرید. جدول تازه با جدول پر از داده یک مسئله نیست.
4. Constraintهای قابل نگهداری را نام‌گذاری کنید. نام خوب برای review، error diagnosis و تغییرات بعدی یک handle پایدار می‌سازد؛ نام‌گذاری به‌تنهایی درست بودن semantics را تضمین نمی‌کند. [PG18-CONSTRAINTS]
5. اثر بر داده‌های موجود را مشخص کنید. بعضی constraintها هنگام اضافه‌شدن داده‌های قبلی را همان لحظه validate می‌کنند و بعضی مسیرهای پشتیبانی‌شده می‌توانند ابتدا NOT VALID اضافه شوند و بعداً validate شوند. [PG18-ALTER-TABLE]
6. Probe مثبت و منفی داشته باشید. قبول‌شدن یک row معتبر و ردشدن یک row نامعتبر دو evidence متفاوت‌اند.
7. Observation را از inference جدا کنید. شکست واقعی VALIDATE CONSTRAINT observation است؛ جمله‌ی «پس این migration در هر production بدون downtime است» inference بدون evidence است.

۴. لایه پایه: پنج ایده‌ای که DDL را قابل‌اعتماد می‌کنند

DDL سطح قرارداد است

CREATE TABLE ساختار و constraintها را تعریف می‌کند و ALTER TABLE تعریف یک جدول موجود را تغییر می‌دهد. [PG18-CREATE-TABLE] [PG18-ALTER-TABLE] اما syntax هدف نهایی نیست. باید بتوانیم هر statement را به invariant و evidence مشخص وصل کنیم.

نام constraint بخشی از maintainability است

PostgreSQL اجازه می‌دهد برای constraint نام مشخص تعریف شود. نام پایدار باعث می‌شود error، review، DROP و VALIDATE به یک rule مشخص اشاره کنند. [PG18-CONSTRAINTS] نمونه‌های مناسب: orders_amount_positive_ck و orders_customer_fk.

DEFAULT با constraint فرق دارد

Default وقتی مقدار مناسب حذف شده باشد یک مقدار را برای write فراهم می‌کند؛ به‌طور کلی سایر مقدارها را ممنوع نمی‌کند. مستندات PostgreSQL نیز تغییر default را برای writeهای بعدی تعریف می‌کنند و آن را rewrite داده‌های قبلی نمی‌دانند. [PG18-ALTER-TABLE] بنابراین status text DEFAULT 'pending' مانع status='nonsense' یا NULL صریح نمی‌شود. اگر مجموعه‌ی مجاز دقیقاً pending/paid/cancelled است، هم requiredness لازم است و هم CHECK مقادیر مجاز. در PostgreSQL، CHECK وقتی expression برابر TRUE یا UNKNOWN باشد pass می‌شود؛ بنابراین CHECK به‌تنهایی NULL را رد نمی‌کند. [PG18-CONSTRAINTS]

داده‌های قبلی مسئله را تغییر می‌دهند

در جدول پرشده، اضافه‌کردن constraint فقط مسئله‌ی syntax نیست. PostgreSQL 18 برای foreign key، CHECK و not-null امکان NOT VALID دارد؛ scan اولیه‌ی داده‌های قدیمی حذف می‌شود اما writeهای جدید یا updateها همچنان باید constraint را رعایت کنند. تا وقتی VALIDATE CONSTRAINT موفق نشده، دیتابیس constraint را برای همه‌ی rowهای قبلی valid فرض نمی‌کند. [PG18-ALTER-TABLE]

این قابلیت عمومی نیست. UNIQUE و PRIMARY KEY در فهرست constraintهای قابل NOT VALID نیستند. بنابراین نباید برای uniqueness syntax ساختگی تولید کرد.

رفتار عملیاتی به subcommand وابسته است

برای همه‌ی شکل‌های ALTER TABLE یک lock profile واحد وجود ندارد. PostgreSQL صریحاً می‌گوید lock موردنیاز به subform بستگی دارد و اگر چند subcommand ترکیب شوند، قوی‌ترین lock لازم اعمال می‌شود. [PG18-ALTER-TABLE] نتیجه‌ی درست این درس «exact subcommand را بررسی کن» است، نه «ALTER TABLE همیشه امن است».

۵. توضیح عمیق: staged validation یک evidence state است، نه نسخه‌ی جادویی migration

فرض کنید orders یک row قدیمی با amount=-5 دارد و می‌خواهید CHECK (amount > 0) اضافه کنید. اگر constraint را به‌صورت validated اضافه کنید، داده‌های فعلی باید همان موقع بررسی شوند و violation مانع موفقیت می‌شود.

برای constraintهای پشتیبانی‌شده، NOT VALID مرحله‌ی نصب rule را از بررسی همه‌ی rowهای قدیمی جدا می‌کند. بعد از نصب، writeهای جدید باید rule را رعایت کنند. سپس VALIDATE CONSTRAINT rowهای موجود را scan می‌کند و فقط در صورت موفقیت constraint برای کل جدول valid می‌شود. [PG18-ALTER-TABLE]

پس سه وضعیت داریم: constraint غایب؛ constraint موجود ولی هنوز برای همه‌ی داده‌های قبلی validate نشده؛ و constraint validated. این state transition برای evidence بسیار مهم است.

اما این state transition به‌تنهایی rollout production را طراحی نمی‌کند. اندازه‌ی جدول، transactionها، lock رقابتی، index، replication، rollback و maintenance window evidence جدا می‌خواهند.

در PostgreSQL 18 برای VALIDATE CONSTRAINT نیز lock مشخص مستند شده است و سایر subformهای ALTER TABLE می‌توانند lock متفاوت یا قوی‌تری بخواهند. [PG18-ALTER-TABLE] بنابراین هیچ نتیجه‌ی محلی را به «zero downtime» تعمیم نمی‌دهیم.

۶. مثال کامل: تبدیل invariant sheet سفارش به named DDL

فرض می‌کنیم نتیجه‌ی درس DAT-001 قبلاً مدل relational پایه را بسته است. customers هویت خودش را دارد و جدول orders ستون‌ها را دارد، اما contract جدید کامل نیست:

• customer_id باید موجود باشد و به مشتری موجود اشاره کند؛
• external_ref باید موجود و unique باشد؛
• amount باید موجود و مثبت باشد؛
• status باید موجود باشد؛ مقدار پیش‌فرض pending دارد اما فقط pending، paid و cancelled مجازند؛
• عمداً یک row قدیمی با مبلغ منفی داریم تا validation واقعی دیده شود.

یک patch Constraint-first می‌تواند چنین ساختاری داشته باشد:

ALTER TABLE orders
  ALTER COLUMN customer_id SET NOT NULL,
  ALTER COLUMN external_ref SET NOT NULL,
  ALTER COLUMN amount SET NOT NULL,
  ALTER COLUMN status SET NOT NULL;

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;

ALTER TABLE orders
  ADD CONSTRAINT orders_external_ref_uq UNIQUE (external_ref);

ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive_ck CHECK (amount > 0) NOT VALID;

ALTER TABLE orders
  ADD CONSTRAINT orders_status_ck
  CHECK (status IN ('pending','paid','cancelled')) NOT VALID;

قرار نیست این block را حفظ کنید. باید قبل از اجرا اثر هر statement بر داده‌های قبلی را پیش‌بینی کنید. status SET NOT NULL لازم است، چون CHECK مقادیر مجاز به‌تنهایی NULL را رد نمی‌کند. uniqueness همان staged path مربوط به CHECK یا foreign key را ندارد. row منفی قدیمی باید اجازه دهد check به‌صورت NOT VALID نصب شود، اما validation بعدی باید تا قبل از repair آن row شکست بخورد.

بعد از repair داده‌ی عمداً نامعتبر:

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive_ck;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_ck;

سپس violation probeها را اجرا کنید: customer ناموجود و customer null، external_ref تکراری و null، مبلغ صفر/منفی/null، status خارج از مجموعه و status null. نتیجه‌ی واقعی PostgreSQL را ثبت کنید؛ error فرضی را از حافظه وارد artifact نکنید.

۷. Failure modeها: پنج میان‌بر که evidence را خراب می‌کنند

Shape-first DDL. ابتدا ستون‌ها ساخته می‌شوند و invariantها «بعداً» قرار است اضافه شوند؛ همان «بعداً» معمولاً محل فراموش‌شدن nullability، uniqueness و existing-data behavior است.

Constraint بی‌نام در schema قابل نگهداری. دیتابیس می‌تواند نام بسازد، اما برای review و تغییر آینده یک نام پایدار traceability بهتری ایجاد می‌کند. [PG18-CONSTRAINTS]

استفاده از DEFAULT به‌جای validation. default مقدار پیشنهادی برای write است، نه لیست مقدارهای مجاز.

برداشت غلط از NOT VALID. برای typeهای پشتیبانی‌شده، writeهای جدید همچنان enforce می‌شوند؛ فقط اعتبار همه‌ی داده‌های قبلی تا validation کامل نشده است. [PG18-ALTER-TABLE]

یک ادعای کلی درباره‌ی همه‌ی ALTERها. lock و scan به exact subcommand و نسخه بستگی دارند. statement واقعی و caveat واقعی را ثبت کنید.

۸. پل Experienced: clever SQL کمتر، reviewability بیشتر

در سطح باتجربه، syntax ساده را کوتاه کنید اما quality evidence را بالا ببرید. naming convention باید قابل پیش‌بینی باشد و به rule اشاره کند، نه ticket موقت یا environment.

برای داده‌های موجود precondition را صریح بنویسید. اگر مکانیزم شما staged validation ندارد، این محدودیت را ثبت کنید؛ syntax غیرواقعی نسازید.

همچنین semantic correctness را از operational acceptability جدا کنید. ممکن است DDL invariant را درست enforce کند ولی برای جدول بزرگ production به rollout متفاوت نیاز داشته باشد. آن سؤال به درس migration/concurrency منتقل می‌شود، نه اینکه در این درس با حدس بسته شود.

۹. Alternative و trade-off

CREATE TABLE در برابر ALTER TABLE. برای ساختار جدید، قرارداد کامل را می‌توان از ابتدا تعریف کرد؛ برای ساختار موجود، داده‌های قبلی هم وارد evidence می‌شوند.

Validation مستقیم در برابر مسیر NOT VALID. اگر داده فعلی clean است و شرایط عملیاتی اجازه می‌دهد، validation مستقیم می‌تواند contract را یک‌مرحله‌ای ببندد. در PostgreSQL 18، foreign key، check و not-null مسیر staged دارند. [PG18-ALTER-TABLE]

Database constraint در برابر application-only validation. application برای UX و workflow مفید است، اما invariantی که باید از همه‌ی write pathها جان سالم ببرد معمولاً به database enforcement هم نیاز دارد. از طرف دیگر هر business rule را نباید به constraint تبدیل کرد.

یک ALTER بزرگ در برابر statementهای کوچک‌تر. ترکیب subcommandها ممکن است convenient باشد، اما lock قوی‌ترِ لازم بین آنها تعیین‌کننده است. [PG18-ALTER-TABLE] برای review، statementهای جدا گاهی evidence و caveat را روشن‌تر می‌کنند؛ اینجا یک deployment style عمومی تجویز نمی‌شود.

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

Guided

در fixture orders قبل از SQL جدول evidence را پر کنید: invariant، ستون‌ها، mechanism، constraint name، existing-data expectation، positive probe، negative probe و caveat.

سپس patch را پیاده کنید. static checkها را اجرا کنید. در PostgreSQL واقعی، نصب constraintها، ردشدن یک amount جدید نامعتبر در حالی که CHECK هنوز not-valid است، یک validation ناموفق به‌دلیل row قدیمی، repair، validation موفق و کل مجموعه‌ی named violation probeها را ثبت کنید.

Repair

یک named constraint را حذف کنید یا status check را فقط با default جایگزین کنید. قبل از اجرا پیش‌بینی کنید کدام write نامعتبر قابل قبول می‌شود. سپس repair کنید و هر دو observation را نگه دارید.

Independent

در سناریوی billing، شماره invoice باید داخل هر tenant unique باشد، amount باید required و positive باشد و invoice باید به tenant موجود اشاره کند. SQL مثال orders را copy نکنید؛ invariant را دوباره model کنید و سؤال‌های operational خارج از scope را جدا بنویسید.

۱۱. Verification و evidence

Artifact کامل باید این موارد را داشته باشد: invariant دامنه، DDL دقیق، constraint name، اثر مورد انتظار بر داده‌های قبلی، probe مثبت و منفی با result واقعی، source ID برای claim version-sensitive، جداسازی observation از inference و residual limitation.

فقط parseشدن SQL proof نیست. screenshot بدون statement و probe proof نیست. متن تولیدشده‌ای که می‌گوید «PostgreSQL این را reject می‌کند» runtime evidence نیست. فیلد observation تا اجرای واقعی باید خالی بماند.

۱۲. Assessment و Transfer

Retrieval: توضیح دهید چرا Constraint-first DDL از invariant شروع می‌شود و یک non-goal درس را نام ببرید.

Application: patch محدود orders را بسازید و record کامل با evidence مثبت/منفی تحویل دهید.

Diagnosis: teammate می‌گوید status DEFAULT 'pending' وضعیت نامعتبر را غیرممکن می‌کند. کوچک‌ترین counterexample و repair را ارائه کنید.

Decision: برای یک constraint مشخص direct validation یا staged path پشتیبانی‌شده را دفاع کنید، بدون ادعای zero-downtime.

Transfer: در billing، tenant-scoped invoice number و positive amount را enforce کنید و سؤال‌های migration/concurrency را جدا به scope بعدی منتقل کنید.

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

• [PG18-CREATE-TABLE] — مرجع رسمی PostgreSQL 18 برای CREATE TABLE و syntax declaration.
• [PG18-CONSTRAINTS] — مستند رسمی PostgreSQL 18 برای semantics و naming constraintها.
• [PG18-ALTER-TABLE] — مرجع رسمی PostgreSQL 18 برای validation داده موجود و lock/scan محدود به subcommand.

Freshness در 2026-09-14 دوباره بررسی شد. source boundary همان سه منبع رسمی باقی مانده و scope منبع توسعه داده نشده است. review بعدی 2026-10-17 است یا زودتر اگر مستندات PostgreSQL یا رفتار lab تغییر کند.