FOREIGN KEY

Kirish: ushbu darsda siz tashqi kalitlar (foreign keys) haqida bilib olasiz va jadval uchun tashqi kalit ustunlarini yaratishda PostgreSQL tashqi kalit cheklovlaridan (foreign key constraints) qanday foydalanishni o'rganasiz.

Tashqi kalitlarga kirish

PostgreSQLda tashqi kalit — bu bitta jadvaldagi boshqa bir jadvalning asosiy kalitiga (PRIMARY KEY) havola qiluvchi ustun yoki ustunlar to'plamidir. U ikki jadval o'rtasidagi bog'lovchi zanjir vazifasini bajaradi.

Masalan, quyidagi products jadvalida brands jadvalining brand_id ustuniga havola qiluvchi brand_id nomli tashqi kalit ustuni mavjud:

Tashqi kalit ustuniga ega bo'lgan jadval farzand jadval (child table) deb ataladi, farzand jadval havola qilayotgan asosiy kalit ustuniga ega jadval esa havola qilingan yoki ota jadval (referenced or parent table) deb yuritiladi. Ushbu misolda brands jadvali havola qilingan yoki ota jadval, products jadvali esa farzand jadval hisoblanadi.

Tashqi kalit farzand jadvaldagi ma'lumotlarning ota jadvaldagi tegishli ma'lumotlarga mos kelishini ta'minlaydi. Masalan, agar sizda products jadvalida brend identifikatori bo'lsa, ushbu brend identifikatori brands jadvalidagi mavjud brend identifikatoriga mos kelishi shart.

Tashqi kalit bog'liqlik yaxlitligini (referential integrity) saqlashga yordam beradi. Masalan, agar mahsulot biror brendga havola qilsa, o'sha brend brands jadvalida albatta mavjud bo'lishi kerak.

Ota jadvaldagi qatorni o'chirganingizda, "yetim qolgan" yozuvlarning (orphaned records) oldini olish uchun farzand jadval bilan to'g'ri ishlashingiz lozim. E'tibor bering, yetim qolgan yozuvlar — bu boshqa jadvaldagi mavjud bo'lmagan asosiy kalit qiymatiga havola qiluvchi yozuvlardir. Masalan, agar brands jadvalidagi biror qator o'chirilsa, products jadvalidagi unga tegishli barcha qatorlar avtomatik ravishda o'chirilishi yoki moslashtirilishi kerak bo'ladi.

Birga-ko'p munosabatida tashqi kalitlar

Birga-ko'p munosabatiga ega ikkita jadvalingiz bo'lsa, munosabatning "ko'p" tomonidagi jadval tashqi kalit ustuniga ega bo'ladi.

Masalan, brands va products jadvallari o'rtasidagi munosabat birga-ko'p munosabatidir. products jadvali "ko'p" tomonida joylashgan; u tashqi kalit ustunini olishi kerak:

products jadvalida brands jadvalining brand_id ustuniga havola qiluvchi qo'shimcha brand_id ustuni mavjud.

Xuddi shunday, categories va products jadvallari o'rtasidagi munosabat ham birga-ko'p munosabatidir. products jadvalida categories jadvalining category_id ustuniga havola qiluvchi qo'shimcha category_id tashqi kalit ustuni bo'lishi kerak:

Bitta jadval products jadvali kabi bir nechta tashqi kalit ustunlariga ega bo'lishi mumkin.

Ko'pga-ko'p munosabatida tashqi kalitlar

Ko'pga-ko'p munosabatini modellashtirishda siz bog'lovchi jadval (link table) deb ataladigan yangi jadval yaratasiz.

Masalan, mahsulotlar (products) va teglar (tags) o'rtasidagi munosabat ko'pga-ko'pdir. Ushbu munosabatni modellashtirish uchun biz product_tags deb nomlangan yangi bog'lovchi jadval yaratamiz:

product_tags jadvalida ikkita tashqi kalit bo'ladi: biri products jadvalidagi product_id ustuniga, ikkinchisi esa tags jadvalidagi tag_id ustuniga havola qiladi.

PostgreSQL tashqi kalit chekloviga kirish

PostgreSQLda jadval yaratishda FOREIGN KEY cheklovidan foydalanib tashqi kalit ustunini belgilashingiz mumkin.

Mana jadval darajasida tashqi kalit cheklovini e'lon qilish sintaksisi:

CONSTRAINT fk_constraint_name
FOREIGN KEY (foreign_key_column)
REFERENCES parent_table(primary_key_column);

Bu sintaksisda:

• Birinchi, CONSTRAINT kalit so'zidan keyin tashqi kalit cheklovi nomini bering. Agar cheklov nomini ko'rsatmasangiz, PostgreSQL avtomatik ravishda unga nom beradi.
• Ikkinchi, FOREIGN KEY kalit so'zidan keyin qavs ichida tashqi kalit ustuni nomini ko'rsating.
• Uchinchi, REFERENCES kalit so'zidan keyin ota jadval nomi va uning asosiy kalit ustunini bering.

Quyidagi misolda brands va products jadvallari yaratiladi:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT,
  CONSTRAINT fk_brands
    FOREIGN KEY (brand_id) 
    REFERENCES brands (brand_id)
);

Bu misolda products jadvalidagi fk_brands cheklovi brand_id ustunini brands jadvalidagi brand_id ustuniga havola qiluvchi tashqi kalit sifatida belgilaydi.

Tashqi kalit cheklovi amalda

Birinchi, brands jadvaliga ikkita qator kiriting:

INSERT INTO brands(name)
VALUES ('Apple'), ('Samsung')
RETURNING *;

Natija:

 brand_id |  name   
----------+---------
        1 | Apple
        2 | Samsung

Ikkinchi, products jadvaliga yangi qator kiriting:

INSERT INTO products(name, price, brand_id)
VALUES('iPhone 14 Pro', 999.99, 1)
RETURNING *;

Natija:

 product_id |     name      | price  | brand_id 
------------+---------------+--------+----------
          1 | iPhone 14 Pro | 999.99 |        1

Agar siz brands jadvalida mavjud bo'lmagan brend identifikatori bilan qator kiritishga urinsangiz, xatolikka duch kelasiz:

INSERT INTO products(name, price, brand_id)
VALUES('iPhone 15 Pro', 1299.99, 11)
RETURNING *;

PostgreSQL quyidagi xatolikni chiqaradi:

insert or update on table "products" violates foreign key constraint "fk_brands"

Tashqi kalit cheklovlari mavjud jadvallarni o'chirish

Tashqi kalit cheklovi orqali boshqa jadvallar tomonidan havola qilingan jadvalni o'chirmoqchi bo'lsangiz, PostgreSQL xatolik qaytaradi:

DROP TABLE brands;

Xatolik:

cannot drop table brands because other objects depend on it

brands jadvalini o'chirish uchun siz quyidagi bosqichlarni bajarasiz:

• Birinchi, brands jadvalidagi brand_id ustuniga havola qiluvchi tashqi kalit cheklovini o'chiring.
• Ikkinchi, brands jadvalining o'zini o'chiring.

Ikkala bosqichni DROP TABLE ... CASCADE buyrug'i yordamida bir martada bajarishingiz mumkin:

DROP TABLE brands CASCADE;

Ushbu so'rov brands jadvalini va unga bog'langan products jadvalidagi tashqi kalit cheklovini o'chiradi. Keling, products jadvalini ham o'chiramiz:

DROP TABLE products;

O'chirish amallari (Deletion actions)

O'chirish amali ota jadvaldagi qatorlar o'chirilganda farzand jadvaldagi qatorlar bilan nima sodir bo'lishini belgilash imkonini beradi:

ON DELETE delete_action;

delete_action quyidagilardan biri bo'lishi mumkin:

• NO ACTION — cheklov buzilishi haqida xatolik chiqaradi. NO ACTION sukut bo'yicha (default) amal hisoblanadi.
• SET NULL — tashqi kalit ustuni qiymatlarini NULL ga o'rnatadi.
• CASCADE — farzand jadvaldagi barcha tegishli qatorlarni o'chirib yuboradi.
• SET DEFAULT — farzand jadvaldagi tashqi kalit ustunlari uchun birlamchi qiymatlarni o'rnatadi.
• RESTRICT — NO ACTION kabi ishlaydi.

1. ON DELETE SET NULL

Birinchi, brands jadvalini qayta yarating:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

Ikkinchi, brands jadvalining brand_id ustuniga havola qiluvchi va ON DELETE SET NULL amali o'rnatilgan products jadvalini yarating:

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id) 
  ON DELETE SET NULL
);

Uchinchi, brands va products jadvallariga qatorlar kiriting:

INSERT INTO brands(name)
VALUES ('Apple'), ('Samsung')
RETURNING *;

INSERT INTO products(name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 1),
  ('iPhone 15 Pro', 1299.99, 1),
  ('Galaxy S23 Ultra', 1299.99, 2)
RETURNING *;

To'rtinchi, brands jadvalidan id = 1 bo'lgan qatorni o'chiring:

DELETE FROM brands WHERE brand_id = 1;

Nihoyat, products jadvalidan ma'lumotlarni oling:

SELECT * FROM products;

Natija:

 product_id |       name       |  price  | brand_id 
------------+------------------+---------+----------
          3 | Galaxy S23 Ultra | 1299.99 |        2
          1 | iPhone 14 Pro    |  999.99 |     NULL
          2 | iPhone 15 Pro    | 1299.99 |     NULL

Natija ON DELETE SET NULL amali o'chirilgan brendga tegishli mahsulotlarning brand_id ustuni qiymatlarini NULL ga o'zgartirganini ko'rsatadi.

2. ON DELETE CASCADE

Birinchi, brands va products jadvallarini o'chiring:

DROP TABLE brands, products;

Ikkinchi, brands jadvalini va ON DELETE CASCADE amaliga ega products jadvalini yarating:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id) 
  ON DELETE CASCADE
);

Uchinchi, jadvallarga ma'lumot kiriting:

INSERT INTO brands(name)
VALUES('Apple'), ('Samsung')
RETURNING *;

INSERT INTO products(name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 1),
  ('iPhone 15 Pro', 1299.99, 1),
  ('Galaxy S23 Ultra', 1299.99, 2)
RETURNING *;

To'rtinchi, brands jadvalidan brand_id = 1 bo'lgan qatorni o'chiring:

DELETE FROM brands 
WHERE brand_id = 1;

Nihoyat, products jadvalidan ma'lumotlarni tekshiring:

SELECT * FROM products;

Natija:

 product_id |       name       |  price  | brand_id 
------------+------------------+---------+----------
          3 | Galaxy S23 Ultra | 1299.99 |        2

Natija ON DELETE CASCADE amali brand_id = 1 bo'lgan mahsulotlarni products jadvalidan avtomatik ravishda o'chirib yuborganini ko'rsatadi.

3. ON DELETE SET DEFAULT

Birinchi, brands va products jadvallarini o'chiring:

DROP TABLE brands, products;

Ikkinchi, brands jadvalini yarating:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

Uchinchi, brand_id INT DEFAULT 1 va ON DELETE SET DEFAULT amali bilan products jadvalini yarating:

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT DEFAULT 1,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id) 
  ON DELETE SET DEFAULT
);

To'rtinchi, brands jadvaliga qatorlar kiriting:

INSERT INTO brands(name)
VALUES
  ('Unknown'),
  ('Apple'), 
  ('Samsung')
RETURNING *;

Bunda id = 1 bo'lgan qator "Unknown" brendi bo'ladi.

Beshinchi, products jadvaliga qatorlar qo'shing:

INSERT INTO products(name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 2),
  ('iPhone 15 Pro', 1299.99, 2),
  ('Galaxy S23 Ultra', 1299.99, 3)
RETURNING *;

Oltinchi, brands jadvalidan brand_id = 2 bo'lgan qatorni o'chiring:

DELETE FROM brands 
WHERE brand_id = 2;

Yettinchi, products jadvalidan ma'lumotlarni oling:

SELECT * FROM products;

Natija:

 product_id |       name       |  price  | brand_id 
------------+------------------+---------+----------
          3 | Galaxy S23 Ultra | 1299.99 |        3
          1 | iPhone 14 Pro    |  999.99 |        1
          2 | iPhone 15 Pro    | 1299.99 |        1

Natija ON DELETE SET DEFAULT amali o'chirilgan brendga tegishli mahsulotlarning brand_id qiymatlarini uning birlamchi qiymatiga, ya'ni 1 ga o'rnatganini ko'rsatadi.

Xulosa

• Tashqi kalit — bu boshqa jadvalning asosiy kalitiga havola qiluvchi ustun yoki ustunlar to'plamidir.
• Bitta jadval bir yoki bir nechta tashqi kalit ustunlariga ega bo'lishi mumkin.
• Jadval uchun tashqi kalit yaratishda PostgreSQL FOREIGN KEY cheklovidan foydalaning.
• Ota jadvaldan qator o'chirilganda farzand jadvaldagi kalit ustunini NULL qilish uchun ON DELETE SET NULL dan foydalaning.
• Ota jadvaldan qator o'chirilganda farzand jadvaldagi barcha bog'liq qatorlarni birga o'chirish uchun ON DELETE CASCADE dan foydalaning.
• Ota jadvaldan qator o'chirilganda farzand jadvaldagi ustunni birlamchi qiymatiga qaytarish uchun ON DELETE SET DEFAULT dan foydalaning.
• Farzand jadvalda bog'liq qatorlar mavjud bo'lsa, ota jadvaldan qator o'chirilishiga yo'l qo'ymaslik uchun ON DELETE NO ACTION dan foydalaning.