Foreign key PostgreSQL'da: REFERENCES, ON DELETE CASCADE va referential actions

Qisqacha: Tashqi kalit (foreign key) — "bu ustundagi qiymat boshqa jadvalda albatta bor" degan cheklov. U bilan baza yo'q foydalanuvchiga vazifa yozishga yo'l qo'ymaydi. Ota qator o'chirilganda nima bo'lishini ON DELETE hal qiladi: CASCADE (bolalar ham o'chadi), SET NULL, SET DEFAULT, RESTRICT yoki sukut NO ACTION (xato). PostgreSQL tashqi kalit ustuniga indeksni o'zi yaratmaydi — uni siz qo'shasiz.

Bu darsda

• REFERENCES bilan ikki jadvalni bog'laysiz va buzilgan bog'lanish xatosini o'qiy olasiz.
• ON DELETE ning besh amalini farqlaysiz va vaziyatga mosini tanlaysiz.
• ON UPDATE CASCADE va DEFERRABLE qachon kerakligini tushuntira olasiz.
• Mavjud "yetim" qatorlarni tozalab, tashqi kalitni xavfsiz qo'shasiz.
• Tashqi kalit ustuniga indeks nega kerakligini bilasiz va vazifalar bazasining holat 14 ini commit qilasiz.

Oldin bilishingiz kerak: CREATE TABLE va cheklovlar, Primary key tanlovi, Relatsion model.

1. Nega bu kerak?

CREATE TABLE va cheklovlar darsida ikki jadval yaratdik: foydalanuvchilar va vazifalar. Vazifada egasi_id ustuni bor — u vazifa kimniki ekanini aytadi. Lekin hozircha bu oddiy son. Baza uni hech narsa bilan solishtirmaydi.

Keling, holat 12 dagi bazada sinab ko'ramiz (biz ishga tushirdik: PG 17.6, Windows 11; bu bo'limdagi va «Yetimlar bo'lsa» bo'limidagi chiqishlar). Aziz ro'yxatdan o'tadi va bitta vazifa yozadi. Yana bitta vazifani esa umuman yo'q foydalanuvchiga (999) yozamiz:

INSERT INTO foydalanuvchilar (email, ism)
VALUES ('aziz@example.com', 'Aziz Karimov')
RETURNING id, ism;

INSERT INTO vazifalar (egasi_id, matn)
VALUES (1, 'Non olish'), (999, 'Egasi yo''q vazifa');

Natija:

 id |     ism      
----+--------------
  1 | Aziz Karimov
(1 row)

INSERT 0 1
INSERT 0 2

Ikkala vazifa ham yozildi. Endi Aziz akkauntini o'chiradi:

DELETE FROM foydalanuvchilar WHERE email = 'aziz@example.com';
SELECT id, email FROM foydalanuvchilar;
SELECT id, egasi_id, matn FROM vazifalar;

Natija:

DELETE 1
 id | email 
----+-------
(0 rows)

 id | egasi_id |       matn        
----+----------+-------------------
  1 |        1 | Non olish
  2 |      999 | Egasi yo'q vazifa
(2 rows)

Foydalanuvchilar jadvali bo'sh, lekin ikkita vazifa qoldi. Ularning egasi yo'q. Bunday qatorlarni yetim qator (orphan row) deyiladi — ota qatori yo'qolgan bola qator.

Bu xavfli. Ertaga yangi foydalanuvchi ro'yxatdan o'tadi. Agar unga ham 1 raqami berilsa, Azizning eski "Non olish" vazifasi birdan uniki bo'lib qoladi. (IDENTITY raqamni qayta bermaydi, lekin qo'lda tiklangan zaxira yoki boshqa jadvalda bunday bo'lishi mumkin.) Hisobotlar ham buziladi: "vazifalar soni" bor, "egalari soni" esa mos kelmaydi.

O'xshatish: kutubxona kartochkasi. Kitob kartochkasida "o'quvchi № 999" deb yozilgan, lekin o'quvchilar ro'yxatida 999 yo'q. Kitob kimda — hech kim bilmaydi. Yaxshi kutubxonachi kartochkani yozishdan oldin ro'yxatni tekshiradi. Tashqi kalit — bazadagi o'sha kutubxonachi.

Diqqat: "Bu tekshiruvni ilova qilsin" degan fikr tabiiy. Lekin bazaga bir nechta yo'l bilan yoziladi: API, admin skripti, import, qo'lda psql. Ilova tekshiruvi faqat ilova orqali kelgan yozuvni ushlaydi. Baza cheklovi esa hammasini ushlaydi.

2. REFERENCES: bog'lanishni e'lon qilish

2.1 Tashqi kalit nima

Tashqi kalit (foreign key, FK) — bir jadval ustunidagi qiymat boshqa jadvalning kalitida mavjud bo'lishini talab qiladigan cheklov.

Ikki tomon bor:

Rasmda shunday ko'rinadi. Chiziqning || tomoni "aynan bitta", o{ tomoni "nol yoki ko'p" degani: bitta foydalanuvchida nol yoki ko'p vazifa, har vazifada aynan bitta ega. Bunday chizmani ER modellash darsida to'liq o'rganamiz.

erDiagram
  foydalanuvchilar ||--o{ vazifalar : "egasi_id"
  foydalanuvchilar {
    bigint id PK
    text email
    text ism
  }
  vazifalar {
    bigint id PK
    bigint egasi_id FK
    text matn
    boolean bajarildi
  }

2.2 Sintaksis: uch shakl

Tashqi kalitni uch joyda yozish mumkin. Ma'nosi bir xil.

-- 1) Ustun yonida (qisqa)
CREATE TABLE vazifalar (
  egasi_id bigint NOT NULL REFERENCES foydalanuvchilar (id)
  -- ...
);

-- 2) Jadval oxirida, nom bilan
CREATE TABLE vazifalar (
  egasi_id bigint NOT NULL,
  -- ...
  CONSTRAINT vazifalar_egasi_id_fkey
    FOREIGN KEY (egasi_id) REFERENCES foydalanuvchilar (id)
);

-- 3) Mavjud jadvalga keyin qo'shish
ALTER TABLE vazifalar
  ADD CONSTRAINT vazifalar_egasi_id_fkey
  FOREIGN KEY (egasi_id) REFERENCES foydalanuvchilar (id);

Uchala blokni birga ishga tushirmang — bu shunchaki uch xil yozuv namunasi.

Bizning loyihada uchinchisi kerak, chunki jadval allaqachon bor. (id) ni tashlab ketsa ham bo'ladi: REFERENCES foydalanuvchilar — ota jadvalning birlamchi kalitiga ishora qiladi.

Nom (vazifalar_egasi_id_fkey) — CREATE TABLE va cheklovlar darsidagi andoza: <jadval>_<ustun>_fkey. Nom bermasangiz, PostgreSQL xuddi shunday nomni o'zi qo'yadi. Lekin migratsiya faylida aniq yozish yaxshi: xato xabarida aynan shu nom chiqadi va uni keyin DROP CONSTRAINT bilan topish oson.

2.3 Qaysi ustunga ishora qilish mumkin

Ota tomonda faqat noyob ustun bo'lishi mumkin — PRIMARY KEY yoki UNIQUE. Aks holda baza "qaysi qatorga ishora?" savoliga javob bera olmaydi. Biz ishga tushirdik (PG 17.6, Windows 11) — ota va bola… nomli sinov jadvallarida. Ular loyiha sxemasiga kirmaydi: shu darsdagi tajribalardan keyin DROP TABLE bola4, bola3, bola, ota; bilan o'chiriladi.

CREATE TABLE ota (id int PRIMARY KEY, kod text);
CREATE TABLE bola2 (kod text REFERENCES ota (kod));

Natija:

CREATE TABLE
ERROR:  there is no unique constraint matching given keys for referenced table "ota"

Tarjimasi: "ota" jadvalida berilgan kalitlarga mos noyob cheklov yo'q. Ya'ni ota.kod takrorlanishi mumkin — unga ishora qilib bo'lmaydi. kod ga UNIQUE qo'shilsa, xato yo'qoladi.

Ustun turlari ham mos bo'lishi kerak. Bizda ikkalasi bigint. Ota bigint, bola int bo'lsa ham ishlaydi, lekin sekinroq taqqoslanadi va 2 milliarddan katta id sig'maydi. Qoida: turini aynan bir xil qiling.

Tekshirib ko'ring: vazifalar.egasi_id foydalanuvchilar.email ga ishora qila oladimi?

2.4 NULL — tekshirilmaydi

Bola ustunida NULL bo'lsa, tashqi kalit uni tekshirmaydi. Bu "bog'lanish yo'q" degani. Biz ishga tushirdik (PG 17.6):

CREATE TABLE bola (ota_id int REFERENCES ota);
INSERT INTO bola VALUES (NULL);

Natija:

CREATE TABLE
INSERT 0 1

Ixtiyoriy bog'lanish uchun bu qulay: masalan, vazifaning "ota vazifasi" bo'lmasligi mumkin. Bunday ustunni ALTER TABLE darsida qo'shamiz. egasi_id esa majburiy: vazifa egasiz bo'lmaydi. Shuning uchun u NOT NULL + tashqi kalit. Ikkalasi birga ishlaydi: biri "bo'sh bo'lmasin", ikkinchisi "bor qiymat bo'lsin".

3. Mavjud jadvalga tashqi kalit qo'shish

3.1 Yetimlar bo'lsa — ALTER yiqiladi

"Nega bu kerak?" bo'limidagi bazaga qaytamiz. Unda ikkita yetim vazifa bor. Tashqi kalitni qo'shib ko'ramiz:

ALTER TABLE vazifalar
  ADD CONSTRAINT vazifalar_egasi_id_fkey
  FOREIGN KEY (egasi_id) REFERENCES foydalanuvchilar (id)
  ON DELETE CASCADE;

Natija:

ERROR:  insert or update on table "vazifalar" violates foreign key constraint "vazifalar_egasi_id_fkey"
DETAIL:  Key (egasi_id)=(1) is not present in table "foydalanuvchilar".

Tarjimasi: "vazifalar" jadvaliga yozish tashqi kalitni buzadi. DETAIL (tafsilot): egasi_id = 1 kaliti "foydalanuvchilar" jadvalida yo'q.

"Insert or update" deyilgani g'alati tuyulishi mumkin — biz hech narsa yozmadik-ku. Gap shunda: ALTER yangi cheklovni qo'shishdan oldin har bir mavjud qatorni xuddi hozir yozilayotgandek tekshiradi. Birinchi buzuvchi qatorda to'xtaydi va xabarda faqat uni ko'rsatadi. Ikkinchi yetim (999) ham bor, lekin xabarda yo'q.

Cheklov qo'shilmadi — jadval avvalgi holida qoldi. PostgreSQL'da DDL buyrug'i ham tranzaksiyali: yarim qo'shilgan cheklov bo'lmaydi.

3.2 Avval yetimlarni topib tozalash

Haqiqiy loyihada ham tartib shunday: avval yetimlarni topasiz, keyin tashqi kalit qo'shasiz.

DELETE FROM vazifalar v
WHERE NOT EXISTS (
  SELECT 1 FROM foydalanuvchilar f WHERE f.id = v.egasi_id
)
RETURNING id, egasi_id;

Natija:

 id | egasi_id 
----+----------
  1 |        1
  2 |      999
(2 rows)

DELETE 2

O'qilishi: "vazifalardan shunday qatorlarni o'chir: ular uchun foydalanuvchilar da id si egasi_id ga teng qator mavjud emas (NOT EXISTS)". Qavs ichidagi SELECT — ichki so'rov. Uni Subquery va LATERAL darsida to'liq o'rganamiz. Hozir shu shablonni eslab qolish yetarli. RETURNING — o'chirilgan qatorlarni ko'rsatadi (INSERT va RETURNING darsida batafsil).

Endi ALTER TABLE muvaffaqiyatli o'tadi.

Maslahat: O'chirish har doim ham to'g'ri yechim emas. Yetimlarni "Arxiv" degan maxsus foydalanuvchiga ko'chirish yoki egasi_id ni NULL qilish ham mumkin. Bu — biznes qarori. Bizda egasiz vazifa ma'nosiz, shuning uchun o'chiramiz. Har holda avval SELECT bilan ko'rib, keyin o'zgartiring (UPDATE va DELETE xavfsiz).

3.3 Katta jadvalda: NOT VALID

Millionlab qatorli jadvalda ADD CONSTRAINT hamma qatorni tekshirgunicha jadvalga yozish to'xtab turadi. Buning ikki bosqichli yo'li bor:

ALTER TABLE vazifalar
  ADD CONSTRAINT vazifalar_egasi_id_fkey
  FOREIGN KEY (egasi_id) REFERENCES foydalanuvchilar (id)
  NOT VALID;   -- yangi yozuvlar darhol tekshiriladi

ALTER TABLE vazifalar
  VALIDATE CONSTRAINT vazifalar_egasi_id_fkey;  -- eskilari

NOT VALID — eski qatorlarni hozircha tekshirmaydi, lekin yangi INSERT/UPDATE darhol tekshiriladi. VALIDATE keyin eski qatorlarni tekshiradi va bu paytda yozuvlarni to'smaydi. Qulflar haqida ALTER TABLE va Qulflar darslarida gaplashamiz. Bizning kichik bazamizga oddiy ADD CONSTRAINT yetarli.

4. ON DELETE: ota o'chirilganda nima bo'ladi

4.1 Besh amal

Tashqi kalit faqat bola tomonni tekshirmaydi. Ota qator o'chirilganda ham u ishga tushadi: "bu qatorga ishora qilayotgan bolalar bor-mi?" Bor bo'lsa — nima qilish kerakligini referential action (bog'lanish amali) aytadi.

flowchart TD
  A["Ota qator o'chirilmoqda"] --> B{"Unga ishora qilgan<br/>bola qator bormi?"}
  B -->|yo'q| C["Ota o'chadi"]
  B -->|bor| D{"ON DELETE ..."}
  D -->|CASCADE| E["Bolalar ham o'chadi"]
  D -->|SET NULL| F["Bolada NULL"]
  D -->|SET DEFAULT| G["Bolada DEFAULT qiymat"]
  D -->|"RESTRICT, NO ACTION"| H["Xato: hech narsa o'chmaydi"]

Diagrammaning pastki qismiga qarang: to'rt yo'ldan uchtasi bolani o'zgartiradi, oxirgisi o'chirishni to'xtatadi.

4.2 CASCADE — bizning tanlov

Holat 14 da vazifalar_egasi_id_fkey ON DELETE CASCADE bilan. Kanon tekshiruvi (PostgreSQL 18.6): uchta vazifasi bor foydalanuvchini o'chiramiz.

INSERT INTO foydalanuvchilar (email, ism)
VALUES ('sinov@example.com', 'Sinov');

INSERT INTO vazifalar (egasi_id, matn)
SELECT id, 'Sinov vazifa ' || n
FROM foydalanuvchilar, generate_series(1, 3) n
WHERE email = 'sinov@example.com';

SELECT count(*) AS oldin FROM vazifalar;
DELETE FROM foydalanuvchilar WHERE email = 'sinov@example.com';
SELECT count(*) AS keyin FROM vazifalar;

Natija:

INSERT 0 1
INSERT 0 3
 oldin 
-------
     3
(1 row)

DELETE 1
 keyin 
-------
     0
(1 row)

DELETE 1 — bitta foydalanuvchi o'chdi, deydi. Lekin u bilan birga uchta vazifa ham ketdi. Psql buni alohida aytmaydi. Bu CASCADE'ning eng katta xavfi: jim o'chiradi. Bitta noto'g'ri DELETE zanjir bo'ylab ko'p jadvalni tozalab yuborishi mumkin. (generate_series(1, 3) — 1, 2, 3 sonlarini qator qilib beradi; bu yerda uchta vazifa yasash uchun.)

Nega baribir CASCADE? Foydalanuvchi "akkauntimni o'chiring" desa, uning vazifalari ham o'chishi kerak — bu shaxsiy ma'lumot. CASCADE'siz ilova avval vazifalarni, keyin foydalanuvchini o'chirishi kerak bo'lardi. Birini unutsa — xato yoki yetim.

4.3 RESTRICT va NO ACTION

Ikkalasi ham o'chirishni to'xtatadi. Kanon misolida (PostgreSQL 18.6) vaqtinchalik jadvallar:

CREATE TEMP TABLE t14_ota (id int PRIMARY KEY);
CREATE TEMP TABLE t14_restrict
  (ota int REFERENCES t14_ota ON DELETE RESTRICT);
CREATE TEMP TABLE t14_noaction
  (ota int REFERENCES t14_ota ON DELETE NO ACTION);
INSERT INTO t14_ota VALUES (1), (2), (3);
INSERT INTO t14_restrict VALUES (1);
INSERT INTO t14_noaction VALUES (2);

DELETE FROM t14_ota WHERE id = 1;
DELETE FROM t14_ota WHERE id = 2;

Natija (oxirgi ikki buyruq):

ERROR:  update or delete on table "t14_ota" violates RESTRICT setting of foreign key constraint "t14_restrict_ota_fkey" on table "t14_restrict"
DETAIL:  Key (id)=(1) is referenced from table "t14_restrict".
ERROR:  update or delete on table "t14_ota" violates foreign key constraint "t14_noaction_ota_fkey" on table "t14_noaction"
DETAIL:  Key (id)=(2) is still referenced from table "t14_noaction".

Tarjimasi: birinchisi — "t14_ota dan o'chirish tashqi kalitning RESTRICT sozlamasini buzadi; id = 1 kalitiga t14_restrict dan ishora qilingan". Ikkinchisi — "tashqi kalitni buzadi; id = 2 ga hali ham ishora qilinmoqda".

PostgreSQL 18 xabarda RESTRICT setting deb aniq aytadi. Biz 17.6 da sinaganimizda RESTRICT xabari ham ikkinchisidagidek edi (violates foreign key constraint … is still referenced). Ma'nosi bir xil.

Farq — qachon tekshirilishida:

• RESTRICT — darhol, qator o'chayotgan zahoti.
• NO ACTION — buyruq oxirida. Agar tekshiruv DEFERRABLE bo'lsa, tranzaksiya oxirigacha kechiktirilishi mumkin (keyingi bo'lim).

Oddiy hayotda ikkalasi bir xil ishlaydi. NO ACTION sukut: ON DELETE yozmasangiz — u.

4.4 SET NULL va SET DEFAULT

SET NULL ni kanon misolida ko'ramiz (PostgreSQL 18.6):

CREATE TEMP TABLE t14_setnull
  (ota int REFERENCES t14_ota ON DELETE SET NULL);
INSERT INTO t14_setnull VALUES (3);
DELETE FROM t14_ota WHERE id = 3;
SELECT * FROM t14_setnull;

Natija (oxirgi ikki buyruq):

DELETE 1
 ota 
-----
    
(1 row)

Qator qoldi, lekin ota endi bo'sh (NULL psql'da bo'sh joy bo'lib ko'rinadi). Bu Bog'lanishlar darsida izoh muallifi uchun kerak bo'ladi: foydalanuvchi o'chsa, uning boshqa vazifalarga yozgan izohlari "o'chirilgan foydalanuvchi" nomi bilan qoladi.

Tuzoq: SET NULL ni NOT NULL ustunga qo'yish. Jadval yaratiladi, xato faqat o'chirishda chiqadi. Biz ishga tushirdik (PG 17.6):

CREATE TABLE bola3 (
  ota_id int NOT NULL REFERENCES ota ON DELETE SET NULL
);
INSERT INTO ota VALUES (1, 'a');
INSERT INTO bola3 VALUES (1);
DELETE FROM ota WHERE id = 1;

Natija. Oxirgi uch qatorga qarang — xato, DETAIL va CONTEXT:

CREATE TABLE
INSERT 0 1
INSERT 0 1
ERROR:  null value in column "ota_id" of relation "bola3" violates not-null constraint
DETAIL:  Failing row contains (null).
CONTEXT:  SQL statement "UPDATE ONLY "public"."bola3" SET "ota_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "ota_id""

Nima bo'ldi? CONTEXT qatori bazaning ichki ishini ochadi: SET NULL aslida bolaga qilingan UPDATE … SET "ota_id" = NULL. DETAIL esa shu yangi qatorni ko'rsatadi — (null). NOT NULL ruxsat bermadi, shuning uchun ota ham o'chmadi.

SET DEFAULT kamdan-kam ishlatiladi. Biz ishga tushirdik (PG 17.6) — "noma'lum" degan 0-ota bilan:

CREATE TABLE bola4 (
  ota_id int DEFAULT 0 REFERENCES ota ON DELETE SET DEFAULT
);
INSERT INTO ota VALUES (0, 'nomalum'), (2, 'b');
INSERT INTO bola4 VALUES (2);
DELETE FROM ota WHERE id = 2;
SELECT * FROM bola4;

Natija:

CREATE TABLE
INSERT 0 2
INSERT 0 1
DELETE 1
 ota_id 
--------
      0
(1 row)

DEFAULT qiymatli ota (0) bo'lishi shart — aks holda tashqi kalit yangi qiymatni ham rad etadi.

Tekshirib ko'ring: Telegram'ga o'xshash ilova. Guruh o'chirilsa — xabarlari? Foydalanuvchi o'chirilsa — uning guruhdagi xabarlari? Har biriga amal tanlang.

5. ON UPDATE va DEFERRABLE

5.1 ON UPDATE: ota kaliti o'zgarsa

ON DELETE ning egizagi — ON UPDATE. Ota qatorning kalit qiymati o'zgarganda ishlaydi. Kanon misoli (PostgreSQL 18.6):

CREATE TEMP TABLE t14_upd
  (ota int REFERENCES t14_ota ON UPDATE CASCADE);
INSERT INTO t14_ota VALUES (30);
INSERT INTO t14_upd VALUES (30);
UPDATE t14_ota SET id = 31 WHERE id = 30;
SELECT * FROM t14_upd;

Natija (oxirgi ikki buyruq):

UPDATE 1
 ota 
-----
  31
(1 row)

Bola ham 30 dan 31 ga o'zgardi. Bizning loyihada ON UPDATE kerak emas: id — GENERATED ALWAYS AS IDENTITY, u hech qachon o'zgarmaydi (Primary key tanlovi). ON UPDATE CASCADE ko'proq "tabiiy kalit" (davlat kodi, mahsulot artikuli) bilan bog'langan jadvallarda uchraydi.

5.2 DEFERRABLE: tekshiruvni COMMIT'gacha kechiktirish

Ba'zan qatorlarni "noto'g'ri" tartibda yozish kerak bo'ladi: avval bola, keyin ota. Masalan, ikki jadval bir-biriga ishora qilsa. Oddiy tashqi kalit bunga yo'l qo'ymaydi.

DEFERRABLE INITIALLY DEFERRED — "tekshiruvni tranzaksiya oxirigacha (COMMIT) kechiktir". Tranzaksiya (transaction) — "hammasi yoki hech biri" bo'lib bajariladigan buyruqlar guruhi: BEGIN dan COMMIT gacha. Uni Tranzaksiya asoslari darsida chuqur o'rganamiz.

Kanon misoli (PostgreSQL 18.6):

CREATE TEMP TABLE t14_def (
  ota int REFERENCES t14_ota DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO t14_def VALUES (10);  -- ota hali yo'q
INSERT INTO t14_ota VALUES (10);  -- COMMIT'gacha qo'shildi
COMMIT;

BEGIN;
INSERT INTO t14_def VALUES (20);
COMMIT;                           -- ota yo'q

Natija:

CREATE TABLE
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
BEGIN
INSERT 0 1
ERROR:  insert or update on table "t14_def" violates foreign key constraint "t14_def_ota_fkey"
DETAIL:  Key (ota)=(20) is not present in table "t14_ota".

Birinchi tranzaksiyada bola otadan oldin yozildi — xato yo'q, chunki COMMIT paytida ota bor edi. Ikkinchisida xato INSERT da emas, aynan COMMIT da chiqdi va butun tranzaksiya bekor bo'ldi.

Bu imkoniyat kerak bo'ladigan holatlar kam: aylanma bog'lanish (bo'lim boshlig'i — xodim, xodim — bo'limda) yoki ko'p qatorni bir vaqtda qayta raqamlash. Bizning sxemada kerak emas.

Diqqat: DEFERRABLE faqat tashqi kalit, UNIQUE, PRIMARY KEY va EXCLUDE uchun bor. CHECK va NOT NULL doim darhol tekshiriladi.

6. Tashqi kalit ustuniga indeks

6.1 PostgreSQL uni o'zi yaratmaydi

Ko'p boshlovchilar shunday deb o'ylaydi: "Tashqi kalit qo'shdim — indeks ham bor". Yo'q. PostgreSQL ota tomonda indeks talab qiladi (PRIMARY KEY yoki UNIQUE — ularda indeks bor). Bola tomondagi egasi_id ga esa indeksni o'zi yaratmaydi. (MySQL'ning InnoDB'si yaratadi — shuning uchun chalkashlik ko'p.)

Indeks (index) — ustun qiymatlari tartiblangan alohida tuzilma; kitob oxiridagi alifbo ko'rsatkichiga o'xshaydi. U bo'lmasa, baza qidirish uchun butun jadvalni boshidan oxirigacha o'qiydi. Ichki tuzilishini Indeks nima darsida ko'ramiz.

Indeks ikki joyda kerak:

1. "Foydalanuvchining vazifalari" so'rovi — WHERE egasi_id = 5. Ilovaning eng ko'p ishlatiladigan so'rovi aynan shu.
2. CASCADE va tekshiruvning o'zi. Foydalanuvchi o'chirilganda baza "unga ishora qilgan vazifalar bormi?" deb qidiradi. Indekssiz bu — 2 mln qatorli jadvalni to'liq o'qish, har bir o'chirilgan foydalanuvchi uchun.

6.2 Indeksi yo'q tashqi kalitlarni topish

Katalogdan (PostgreSQL o'zi haqidagi ma'lumotni saqlaydigan jadvallar) indeksi yo'q tashqi kalitlarni topadigan so'rov. Hozir har qatorini tushunish shart emas — uni "tayyor asbob" sifatida saqlab qo'ying. U bitta ustunli tashqi kalitlarni tekshiradi. Biz ishga tushirdik (PG 17.6) — tashqi kalit qo'shildi, indeks hali yo'q:

SELECT c.conrelid::regclass AS jadval,
       c.conname AS kalit, a.attname AS ustun
FROM pg_constraint c
JOIN pg_attribute a
  ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND i.indkey[0] = c.conkey[1]);

Natija:

  jadval   |          kalit          |  ustun   
-----------+-------------------------+----------
 vazifalar | vazifalar_egasi_id_fkey | egasi_id
(1 row)

CREATE INDEX vazifalar_egasi_id_idx ON vazifalar (egasi_id); dan keyin xuddi shu so'rov (0 rows) qaytardi. Amaliyot: real loyiha sxemasi darsida butun sxemani shunday review qilamiz.

Indeksning narxi ham bor: har INSERT da u ham yangilanadi va disk egallaydi. Lekin tashqi kalit ustunida u deyarli doim o'zini oqlaydi.

Tekshirib ko'ring: Nega ota tomonda indeks majburiy, bola tomonda esa yo'q?

7. SQL qadami: holat 14 — commit

7.1 Migratsiya fayli

Loyihangizdagi sql/ papkasiga ikkinchi fayl qo'shiladi. Bu kanon fayli, aynan shunday:

-- 002-tashqi-kalitlar.sql — #14 (DB-11): vazifa egasiz qolmasin.
-- ON DELETE CASCADE: foydalanuvchi o'chirilsa — uning vazifalari
-- ham (vazifa egasiz ma'nosiz).
ALTER TABLE vazifalar
  ADD CONSTRAINT vazifalar_egasi_id_fkey
  FOREIGN KEY (egasi_id) REFERENCES foydalanuvchilar (id)
  ON DELETE CASCADE;

-- Postgres FK ustuniga indeksni O'ZI yaratmaydi. Kerak:
-- (1) "foydalanuvchining vazifalari" so'rovi, (2) foydalanuvchi
-- o'chirilganda CASCADE vazifalarni qidiradi.
CREATE INDEX vazifalar_egasi_id_idx ON vazifalar (egasi_id);

Kanondagi izoh qatorlari telefonga sig'ishi uchun qayta bo'lindi, ma'nosi o'zgarmadi. 001 fayli o'zgarmaydi: qo'llangan migratsiya faylini tahrirlamaymiz, yangisini qo'shamiz. Faylni PostgreSQL o'rnatish va ulanish darsidagi ulanish bilan qo'llang:

psql -X -1 -v ON_ERROR_STOP=1 \
  -d postgres://postgres@127.0.0.1:5499/vazifalar \
  -f sql/002-tashqi-kalitlar.sql

-1 — butun fayl bitta tranzaksiyada (yarmi qo'llanib qolmaydi), ON_ERROR_STOP=1 — birinchi xatoda to'xtaydi (psql chuqur). Bazangizda "Nega bu kerak?" bo'limidagi kabi yetimlar bo'lsa, fayl xato beradi — avval ularni tozalang.

7.2 Tekshiruv

Kanon tekshiruvi (holat 14, PostgreSQL 18.6) — \d vazifalar oxiri:

Indexes:
    "vazifalar_pkey" PRIMARY KEY, btree (id)
    "vazifalar_egasi_id_idx" btree (egasi_id)
Check constraints:
    "vazifalar_matn_check" CHECK (matn = btrim(matn) AND char_length(matn) >= 1 AND char_length(matn) <= 100)
Foreign-key constraints:
    "vazifalar_egasi_id_fkey" FOREIGN KEY (egasi_id) REFERENCES foydalanuvchilar(id) ON DELETE CASCADE

Va yo'q foydalanuvchiga vazifa endi yozilmaydi:

ERROR:  insert or update on table "vazifalar" violates foreign key constraint "vazifalar_egasi_id_fkey"
DETAIL:  Key (egasi_id)=(999) is not present in table "foydalanuvchilar".

Holat 14 — bo'sh sxema (namuna ma'lumot keyingi darsda). U noldan qurilganda ham, namuna bilan ham xatosiz o'tadi.

7.3 Commit

git add sql/002-tashqi-kalitlar.sql
git commit -m "db: vazifalar.egasi_id — tashqi kalit va indeks (002)" \
  -m "ON DELETE CASCADE"

Kanon commit xabari bitta qator: db: vazifalar.egasi_id — tashqi kalit (ON DELETE CASCADE) va indeks (002). U 72 belgidan uzun bo'lgani uchun sarlavha va tanaga bo'ldik (commit xabarlari).

8. Ko'p uchraydigan xatolar

9. Mashqlar

1-mashq (oson): qaysi amal?

Har vaziyatga ON DELETE amalini yozing (CASCADE, SET NULL yoki RESTRICT):

• Do'kon: buyurtma o'chirilsa — uning qatorlari (mahsulot, soni):
• Bank: mijozni o'chirishga urinish, unda to'lovlar tarixi bor:
• Taksi: haydovchi ishdan ketdi, uning eski safarlari qolishi kerak:
• Sukut bo'yicha (hech narsa yozilmasa) qaysi amal:

2-mashq (o'rta): loyiha a'zolari

Holat 14 bazasida ikki jadval yarating:

• loyihalar (id — IDENTITY, nom);
• loyiha_azolari (loyiha_id, foydalanuvchi_id, ikkalasi birgalikda PRIMARY KEY).

Shartlar: loyiha o'chirilsa — a'zoliklari ham o'chsin. A'zo bo'lgan foydalanuvchini o'chirib bo'lmasin (sukut amali). foydalanuvchi_id ga indeks qo'shing. So'ng Malika'ni yozib, "Bitiruv ishi" loyihasiga a'zo qiling va ikkala o'chirish qoidasini sinang.

Ishora: ikki ustunli PRIMARY KEY (a, b) — CREATE TABLE darsida; INSERT … SELECT — keyingi darsda, hozir yechimdagidek yozing.

CREATE TABLE loyihalar (
  id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom text   NOT NULL
);
CREATE TABLE loyiha_azolari (
  loyiha_id        bigint NOT NULL
    REFERENCES loyihalar ON DELETE CASCADE,
  foydalanuvchi_id bigint NOT NULL
    REFERENCES foydalanuvchilar,  -- sukut: NO ACTION
  PRIMARY KEY (loyiha_id, foydalanuvchi_id)
);
CREATE INDEX loyiha_azolari_foydalanuvchi_id_idx
  ON loyiha_azolari (foydalanuvchi_id);

INSERT INTO foydalanuvchilar (email, ism)
VALUES ('malika@example.com', 'Malika');
INSERT INTO loyihalar (nom) VALUES ('Bitiruv ishi');
INSERT INTO loyiha_azolari
SELECT l.id, f.id FROM loyihalar l, foydalanuvchilar f;

-- a'zo foydalanuvchini o'chirib bo'lmaydi
DELETE FROM foydalanuvchilar WHERE email = 'malika@example.com';
-- loyiha o'chsa — a'zoliklar ham
DELETE FROM loyihalar WHERE nom = 'Bitiruv ishi';
SELECT count(*) AS azoliklar FROM loyiha_azolari;
-- endi foydalanuvchini o'chirsa bo'ladi
DELETE FROM foydalanuvchilar WHERE email = 'malika@example.com';

ERROR:  update or delete on table "foydalanuvchilar" violates foreign key constraint "loyiha_azolari_foydalanuvchi_id_fkey" on table "loyiha_azolari"
DETAIL:  Key (id)=(1) is still referenced from table "loyiha_azolari".
 azoliklar 
-----------
         0
(1 row)

3-mashq (qiyin): yetimlarni tozalab, tashqi kalit qo'shish

Holat 12 bazasi (tashqi kalitsiz). Unda Jasur va uning bitta vazifasi, yana ikkita yetim vazifa (egasi_id 500 va 501) bor. Bitta tranzaksiyada (BEGIN … COMMIT): yetimlarni o'chirib ro'yxatini chiqaring, tashqi kalit va indeks qo'shing. Keyin qolgan vazifalar sonini chiqaring. Tranzaksiya nega kerak — o'zingiz izohlang.

-- Tayyorgarlik
INSERT INTO foydalanuvchilar (email, ism)
VALUES ('jasur@example.com', 'Jasur');
INSERT INTO vazifalar (egasi_id, matn)
SELECT id, 'Hisobot yozish' FROM foydalanuvchilar;
INSERT INTO vazifalar (egasi_id, matn)
VALUES (500, 'Yetim 1'), (501, 'Yetim 2');

-- Migratsiya: hammasi bitta tranzaksiyada
BEGIN;
DELETE FROM vazifalar v
WHERE NOT EXISTS (
  SELECT 1 FROM foydalanuvchilar f WHERE f.id = v.egasi_id
)
RETURNING v.id, v.egasi_id, v.matn;
ALTER TABLE vazifalar
  ADD CONSTRAINT vazifalar_egasi_id_fkey
  FOREIGN KEY (egasi_id) REFERENCES foydalanuvchilar (id)
  ON DELETE CASCADE;
CREATE INDEX vazifalar_egasi_id_idx ON vazifalar (egasi_id);
COMMIT;

SELECT count(*) AS qolgan_vazifalar FROM vazifalar;

 id | egasi_id |  matn   
----+----------+---------
  2 |      500 | Yetim 1
  3 |      501 | Yetim 2
(2 rows)

 qolgan_vazifalar 
------------------
                1
(1 row)

4-mashq: SQL qadami

sql/002-tashqi-kalitlar.sql ni yozing, bazangizga qo'llang (psql -1 -v ON_ERROR_STOP=1 -f …). \d vazifalar da Foreign-key constraints va vazifalar_egasi_id_idx borligini tekshiring. Yo'q foydalanuvchiga (999) vazifa yozib, xatoni ko'ring. So'ng commit qiling («SQL qadami» bo'limidagi buyruq).

10. Real ishda

• Har backend loyihada — foydalanuvchi → buyurtma → buyurtma qatori, kanal → xabar, kurs → dars. Tashqi kalitsiz sxema code review'da birinchi savolni oladi: "Yetimlarni kim kafolatlaydi?"
• ORM'lar (Prisma, Drizzle, TypeORM — 27-qismda o'rganamiz) onDelete: Cascade kabi yozuvni aynan shu ON DELETE ga aylantiradi. Ba'zi ORM'lar "tashqi kalitni ilovada emulyatsiya qilish" rejimini taklif qiladi — bu kafolatni yo'qotadi.
• Indeks review — "FK bor, indeks yo'q" eng ko'p topiladigan sekinlik sabablaridan. Yuqoridagi katalog so'rovi kabi tekshiruvlar DBA asboblarida tayyor turadi.
• Intervyu savollari: "CASCADE va RESTRICT farqi?", "RESTRICT va NO ACTION farqi?", "Postgres FK ustuniga indeks yaratadimi?" — oxirgisiga ko'pchilik "ha" deb xato javob beradi.

Xulosa

• Tashqi kalit — REFERENCES ota (kalit): bola ustunidagi har qiymat otada bo'lishini baza kafolatlaydi; NULL tekshirilmaydi.
• Ota ustuni noyob bo'lishi shart (PRIMARY KEY/UNIQUE); turlar bir xil bo'lsin.
• Mavjud jadvalga qo'shishda yetimlar ALTER ni yiqitadi — avval NOT EXISTS bilan toping va hal qiling; katta jadvalda NOT VALID + VALIDATE.
• ON DELETE: CASCADE (jim o'chiradi!), SET NULL, SET DEFAULT, RESTRICT (darhol), NO ACTION (sukut, kechiktirilishi mumkin).
• ON UPDATE CASCADE — o'zgaradigan kalitlar uchun; DEFERRABLE INITIALLY DEFERRED — tekshiruv COMMIT da.
• PostgreSQL bola ustuniga indeks yaratmaydi — vazifalar_egasi_id_idx ni o'zimiz qo'shdik; holat 14 commit qilindi.

Keyingi dars: INSERT va RETURNING — bazaga 8 foydalanuvchi va 40 vazifadan iborat namuna ma'lumot yozamiz, ko'p qatorli INSERT, INSERT … SELECT va yaratilgan qatorni bir so'rovda qaytarib olishni o'rganamiz.

Manbalar

• PostgreSQL 18 hujjati — Constraints: Foreign Keys (referential action'lar, indeks haqida eslatma)
• PostgreSQL 18 hujjati — CREATE TABLE (REFERENCES, DEFERRABLE, RESTRICT va NO ACTION farqi)
• PostgreSQL 18 hujjati — ALTER TABLE (NOT VALID, VALIDATE CONSTRAINT)