FOREIGN KEY — CASCADE, SET NULL, RESTRICT

~20 daq · MySQL · DDL — Tuzilma Boshqarish

Muammodan boshlaylik

buyurtmalar jadvalida mijoz_id = 47 turibdi. Lekin mijozlar jadvalida 47-mijoz yo'q — kimdir uni o'chirib yuborgan. Endi bu buyurtma kimniki? Qayerga yetkazish kerak? Hisobotda "noma'lum mijoz" paydo bo'ldi.

Yoki: kimdir mijoz_id = 9999 deb buyurtma yaratdi — bunday mijoz hech qachon bo'lmagan.

Bunday "yetim" yozuvlar — referential integrity (havolalar yaxlitligi) buzilishi. Foreign key — bazaga "bu ustundagi har bir qiymat boshqa jadvalda albatta mavjud bo'lishi kerak" degan qoidani o'rgatadi.

Foreign key yaratish

CREATE TABLE buyurtmalar (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mijoz_id   INT UNSIGNED NOT NULL,
    yaratilgan DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_buyurtma_mijoz
        FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id)
);

• mijoz_id — bola jadvaldagi ustun (foreign key)
• mijozlar(id) — ota jadvaldagi ustun (odatda primary key)
• Ikkala ustunning turi bir xil bo'lishi shart: INT UNSIGNED ↔ INT UNSIGNED

Endi baza ikki narsani nazorat qiladi:

1. Mavjud bo'lmagan otaga ishora qilib bo'lmaydi:

INSERT INTO buyurtmalar (mijoz_id) VALUES (9999);
-- ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

2. Bolasi bor otani o'chirib bo'lmaydi:

DELETE FROM mijozlar WHERE id = 1;   -- Azizning buyurtmalari bor
-- ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails

Ota o'chirilganda nima bo'ladi?

Ikkinchi holat — eng qiziq savol. Mijoz o'chirilsa, uning buyurtmalari bilan nima qilish kerak? Javob biznesga bog'liq, va MySQL bir nechta variant beradi:

FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id)
    ON DELETE <amal>
    ON UPDATE <amal>

RESTRICT — himoya

FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id) ON DELETE RESTRICT

Buyurtmasi bor mijozni o'chirib bo'lmaydi. Bu to'g'ri: buxgalteriya hisobotlari, soliq — o'tgan buyurtmalar saqlanishi shart. Mijoz "akkauntimni o'chiring" desa — odatda uning shaxsiy ma'lumotlari anonimlashtiriladi, qatori esa qoladi.

ON DELETE yozilmasa — standart holat aynan shu.

CASCADE — zanjirli o'chirish

CREATE TABLE buyurtma_qatorlari (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    buyurtma_id  INT UNSIGNED NOT NULL,
    mahsulot_id  INT UNSIGNED NOT NULL,
    soni         SMALLINT UNSIGNED NOT NULL,
    FOREIGN KEY (buyurtma_id) REFERENCES buyurtmalar(id) ON DELETE CASCADE,
    FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar(id) ON DELETE RESTRICT
);

DELETE FROM buyurtmalar WHERE id = 5;
-- 5-buyurtmaning barcha qatorlari ham avtomatik o'chdi

Buyurtmasiz buyurtma qatori — ma'nosiz. Uni alohida o'chirib yurish shart emas.

Mahsulot esa RESTRICT: sotilgan mahsulotni o'chirib bo'lmaydi — aks holda tarixiy buyurtmalar buziladi.

[!WARNING] CASCADE xavfli bo'lishi mumkin — zanjir uzoqqa ketadi. mijozlar → buyurtmalar → buyurtma_qatorlari hammasi CASCADE bo'lsa, bitta mijozni o'chirish yuzlab qatorni jimgina yo'q qiladi. CASCADE ni faqat "bola otaning ajralmas qismi" bo'lgan joyda ishlating.

SET NULL — bog'lanishni uzish

CREATE TABLE mahsulotlar (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    toifa_id  INT UNSIGNED NULL,           -- NULL bo'lishi mumkin bo'lishi shart!
    nom       VARCHAR(200) NOT NULL,
    FOREIGN KEY (toifa_id) REFERENCES toifalar(id) ON DELETE SET NULL
);

DELETE FROM toifalar WHERE id = 3;
-- 3-toifadagi mahsulotlar qoldi, toifa_id = NULL ("toifasiz")

Toifa o'chirildi — mahsulotlar yo'qolmasligi kerak, ular shunchaki "toifasiz" bo'lib qoladi.

ON UPDATE

Otaning primary key i o'zgarganda nima bo'lishi. id lar odatda hech qachon o'zgarmaydi, shuning uchun bu kam ahamiyatli. Agar foreign key id ga emas, masalan, kod ga ishora qilsa — ON UPDATE CASCADE foydali.

Mavjud jadvalga qo'shish

ALTER TABLE buyurtmalar
    ADD CONSTRAINT fk_buyurtma_mijoz
    FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id) ON DELETE RESTRICT;

Agar jadvalda allaqachon "yetim" qatorlar bo'lsa — xato beradi. Avval ularni topish kerak:

SELECT b.id, b.mijoz_id
FROM buyurtmalar b
LEFT JOIN mijozlar m ON m.id = b.mijoz_id
WHERE m.id IS NULL;

(LEFT JOIN — 16-darsda.)

O'chirish:

ALTER TABLE buyurtmalar DROP FOREIGN KEY fk_buyurtma_mijoz;

Mana nima uchun cheklovlarga nom berish kerak — aks holda MySQL buyurtmalar_ibfk_1 kabi nom qo'yadi va uni topish kerak bo'ladi.

Foreign key va indeks

MySQL foreign key ustuniga avtomatik indeks yaratadi (agar yo'q bo'lsa). Bu muhim: har bir DELETE FROM mijozlar da MySQL buyurtmalar dan shu mijozning qatorlarini qidiradi — indekssiz bu butun jadvalni ko'rib chiqish bo'lardi.

Vaqtincha o'chirish

Katta ma'lumotni import qilishda (backup tiklash) jadvallar tartibini kuzatish qiyin. Vaqtincha tekshiruvni o'chirish mumkin:

SET FOREIGN_KEY_CHECKS = 0;
-- import...
SET FOREIGN_KEY_CHECKS = 1;

[!WARNING] Bu faqat sizning sessiyangiz uchun va faqat ishonchli ma'lumot import qilishda. Yoqqaningizda MySQL o'tgan ma'lumotlarni qayta tekshirmaydi — yetim qatorlar kirib qolsa, ular qoladi.

Nima uchun shunday?

Nima uchun foreign key ni ilovada tekshirish yetarli emas?

Xuddi UNIQUE dagi kabi: ikkita bir vaqtdagi so'rov. Biri mijozni o'chiryapti, ikkinchisi xuddi shu mijozga buyurtma yaratyapti. Ilova ikkalasida ham "hammasi joyida" deb tekshiradi — va natijada yetim buyurtma. Baza esa ikkala amalni qulflar orqali ketma-ket bajaradi va ikkinchisini rad etadi. Bundan tashqari, foreign key — hujjat: sxemaga qaragan har bir dasturchi jadvallar qanday bog'langanini darhol ko'radi.

Xulosa

Eslab qoling:

• FOREIGN KEY (ustun) REFERENCES ota(id) — bola qiymati otada albatta bo'lishi kerak
• Turlar aynan mos bo'lishi shart: INT UNSIGNED ↔ INT UNSIGNED
• RESTRICT (standart) — bolasi bor otani o'chirib bo'lmaydi
• CASCADE — bolalar ham o'chadi; faqat "ajralmas qism" uchun
• SET NULL — bog'lanish uziladi; ustun NULL bo'la olishi kerak
• CONSTRAINT fk_nom — nom bering
• Avval ota jadval, keyin bola jadval

Keyingi dars

8-dars: ALTER TABLE — ustun qo'shish, o'zgartirish, o'chirish. DROP