کلید خارجی، ON DELETE و CHECK constraint

بگذارید دیتابیس از داده دفاع کند

برنامه‌ها عوض می‌شوند، اسکریپت‌های ایمپورت دستی نوشته می‌شوند و کسی مستقیم در DBeaver ردیف پاک می‌کند. قیدها (constraints) آخرین خط دفاع‌اند: سفارشی بدون مشتری، قلم سفارشی با تعداد منفی یا تخفیف بیش از صد درصد اصلاً نباید قابل ذخیره باشد.

کلید خارجی

CREATE TABLE customers (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status      VARCHAR(20)  NOT NULL DEFAULT 'draft',
  discount    DECIMAL(5,2) NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
    REFERENCES customers (id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT chk_orders_status CHECK (status IN ('draft','paid','weaving','shipped','cancelled')),
  CONSTRAINT chk_orders_discount CHECK (discount BETWEEN 0 AND 100)
);

INSERT INTO orders (customer_id) VALUES (999);
-- ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
INSERT INTO orders (customer_id, discount) VALUES (1, 120);
-- ERROR 3819: Check constraint 'chk_orders_discount' is violated.

رفتار هنگام حذف والد

برای داده‌ی مالی CASCADE را با احتیاط به کار ببرید؛ یک DELETE اشتباه روی مشتری نباید تاریخچه‌ی فروش را پاک کند. در سیستم‌های تجاری به‌جای حذف، معمولاً «حذف نرم» (ستون is_active یا deleted_at) انجام می‌شود.

شرط‌های لازم برای ساخت کلید خارجی

• هر دو جدول InnoDB باشند.
• نوع ستون‌ها دقیقاً یکسان باشد: INT با INT UNSIGNED سازگار نیست (ERROR 3780).
• ستون والد کلید اصلی یا دارای ایندکس UNIQUE باشد.
• charset و collation ستون‌های متنی یکسان باشد.

ورود داده‌ی حجیم

SET FOREIGN_KEY_CHECKS = 0;   -- فقط در همین نشست
SOURCE big_import.sql;
SET FOREIGN_KEY_CHECKS = 1;   -- داده‌ی واردشده دوباره بررسی نمی‌شود!

-- پیدا کردن یتیم‌ها پس از ورود
SELECT o.id FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL;

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

• حذف و به‌روزرسانی‌هایی که با CASCADE روی جدول فرزند انجام می‌شوند، Triggerهای جدول فرزند را اجرا نمی‌کنند؛ اگر لاگ حسابرسی را با Trigger می‌سازید، این حذف‌ها در لاگ نمی‌آیند.
• InnoDB برای ستون کلید خارجی اگر ایندکس نباشد خودش یکی می‌سازد؛ بدون این ایندکس، هر حذف والد به اسکن کامل جدول فرزند و قفل‌های گسترده منجر می‌شد.
• تا نسخه‌ی 8.0.15، CHECK خوانده و بی‌صدا نادیده گرفته می‌شد؛ جدولی که در آن نسخه‌ها ساخته شده، قید را ندارد. با SHOW CREATE TABLE مطمئن شوید.
• CHECK نمی‌تواند به جدول دیگر، زیرکوئری یا توابع غیرقطعی مثل NOW() ارجاع دهد؛ برای «تاریخ تحویل بعد از امروز» از منطق برنامه یا Trigger استفاده کنید.
• با ALTER TABLE orders ALTER CHECK chk_orders_discount NOT ENFORCED; می‌توانید قید را موقتاً غیرفعال کنید بدون اینکه تعریفش را از دست بدهید.