Jadval yaratish va cheklovlar - PostgreSQL

Cheklov (constraint) - bu bazaga aytilgan qoida: "bunday ma'lumot bu yerga tusha olmaydi". Uni ilovada emas, bazada yozish muhim.

Sabab oddiy: bitta bazaga bir nechta ilova murojaat qiladi - veb sayt, mobil ilova, hisobot skripti, qo'lda yozilgan so'rov. Ilovadagi tekshiruvni chetlab o'tish oson, bazadagi cheklovni esa hech kim chetlab o'ta olmaydi.

Har cheklov bitta aniq savolga javob beradi

Jadval yaratish #

Kutubxona uchun kitoblar jadvalini quramiz:

SQL

CREATE TABLE muallif (
    id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism    text NOT NULL,
    davlat text NOT NULL DEFAULT 'O''zbekiston'
);

CREATE TABLE kitob (
    id         int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sarlavha   text NOT NULL,
    muallif_id int NOT NULL REFERENCES muallif(id) ON DELETE RESTRICT,
    nashr_yili int CHECK (nashr_yili BETWEEN 1400 AND 2100),
    narx       numeric(10,2) NOT NULL CHECK (narx >= 0),
    sahifa     int CHECK (sahifa > 0),
    isbn       text UNIQUE
);

INSERT INTO muallif (ism, davlat) VALUES
    ('Abdulla Qodiriy', 'O''zbekiston'),
    ('Cho''lpon', 'O''zbekiston'),
    ('Lev Tolstoy', 'Rossiya');

INSERT INTO kitob
    (sarlavha, muallif_id, nashr_yili, narx, sahifa, isbn)
VALUES
    ('O''tkan kunlar',   1, 1926, 45000, 384, '978-9943-01-001-1'),
    ('Mehrobdan chayon', 1, 1929, 38000, 320, '978-9943-01-002-8'),
    ('Kecha va kunduz',  2, 1936, 42000, 288, '978-9943-01-003-5'),
    ('Urush va tinchlik',3, 1869, 95000, 1225, '978-9943-01-004-2');

Sxemani o'qib chiqing - har qator bitta qoidani bildiradi:

Yozuv	Qoida
`NOT NULL`	Sarlavhasiz kitob bo'lmaydi
`DEFAULT 'O''zbekiston'`	Davlat ko'rsatilmasa - shu
`CHECK (narx >= 0)`	Manfiy narx mantiqsiz
`UNIQUE`	Bitta ISBN - bitta kitob
`REFERENCES muallif(id)`	Muallif haqiqatan mavjud bo'lishi shart

Natijani ko'ramiz:

SQL

SELECT id, sarlavha, nashr_yili, narx FROM kitob ORDER BY id;

Natija

 id |     sarlavha      | nashr_yili |   narx
----+-------------------+------------+----------
  1 | O'tkan kunlar     |       1926 | 45000.00
  2 | Mehrobdan chayon  |       1929 | 38000.00
  3 | Kecha va kunduz   |       1936 | 42000.00
  4 | Urush va tinchlik |       1869 | 95000.00
(4 rows)

Cheklov ishlayotganini ko'rish #

Endi ataylab noto'g'ri ma'lumot kiritamiz:

SQL

INSERT INTO kitob (sarlavha, muallif_id, narx) VALUES ('Sinov', 1, -100);

Natija

ERROR:  new row for relation "kitob" violates check constraint "kitob_narx_check"
DETAIL:  Failing row contains (5, Sinov, 1, null, -100.00, null, null).

Baza qatorni qabul qilmadi va qaysi cheklov buzilganini aniq aytdi: kitob_narx_check.

Xuddi shunday, mavjud bo'lmagan muallifga bog'lash ham ishlamaydi:

SQL

INSERT INTO kitob (sarlavha, muallif_id, narx) VALUES ('Sinov', 999, 10000);

Natija

ERROR:  insert or update on table "kitob" violates foreign key constraint "kitob_muallif_id_fkey"
DETAIL:  Key (muallif_id)=(999) is not present in table "muallif".

Cheklovga o'zingiz nom bering

Yuqorida PostgreSQL nomni o'zi yasadi: kitob_narx_check. Bu tushunarli, lekin har doim ham emas.

Nomni o'zingiz bersangiz, xato xabari ancha foydali bo'ladi:

SQL

CONSTRAINT narx_manfiy_bolmasin CHECK (narx >= 0)

Endi xato xabarida narx_manfiy_bolmasin ko'rinadi - va uni foydalanuvchiga ko'rsatiladigan matnga bog'lash oson bo'ladi.

Bu ayniqsa migratsiyalarda muhim: nomi ma'lum cheklovni keyin ALTER TABLE ... DROP CONSTRAINT bilan o'chirish mumkin. Avtomatik nom esa versiyalar orasida o'zgarishi mumkin.

Tashqi kalit va o'chirish xatti-harakati #

REFERENCES yozganda, bog'langan qator o'chirilsa nima bo'lishini ham aytish kerak:

Yozuv	Ota qator o'chirilganda
`ON DELETE RESTRICT`	Ruxsat berilmaydi (standart)
`ON DELETE CASCADE`	Bola qatorlar ham o'chiriladi
`ON DELETE SET NULL`	Bog'lanish `NULL` ga aylanadi

Bizning jadvalda RESTRICT turibdi, ya'ni kitobi bor muallifni o'chirib bo'lmaydi:

SQL

DELETE FROM muallif WHERE id = 1;

Natija

ERROR:  update or delete on table "muallif" violates RESTRICT setting of foreign key constraint "kitob_muallif_id_fkey" on table "kitob"
DETAIL:  Key (id)=(1) is referenced from table "kitob".

CASCADE ni ehtiyot bo'lib ishlating

ON DELETE CASCADE qulay ko'rinadi: ota qatorni o'chirsangiz, bolalari ham o'z-o'zidan ketadi.

Muammo shundaki, u jimgina ishlaydi va zanjir bo'ylab tarqaladi. Bitta foydalanuvchini o'chirdingiz - uning buyurtmalari, to'lovlari va butun tarixi ham ketdi.

Amaliy qoida:

Bog'lanish	Tavsiya
Haqiqatan tobe ma'lumot (buyurtma qatorlari)	`CASCADE` mos
Mustaqil qiymatga ega yozuvlar (to'lovlar, jurnal)	`RESTRICT`

Shubha bo'lsa RESTRICT tanlang: baza sizni to'xtatadi va siz o'ylab ko'rasiz.

Hisoblanadigan ustunlar #

Ba'zi qiymatlarni saqlash shart emas - ularni boshqa ustunlardan hisoblash mumkin:

SQL

CREATE TABLE mahsulot (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom      text NOT NULL,
    narx     numeric(10,2) NOT NULL,
    soni     int NOT NULL,
    jami     numeric(12,2) GENERATED ALWAYS AS (narx * soni) STORED
);

INSERT INTO mahsulot (nom, narx, soni) VALUES
    ('Daftar', 5000, 12), ('Ruchka', 3000, 40);

SELECT nom, narx, soni, jami FROM mahsulot ORDER BY id;

Natija

CREATE TABLE
INSERT 0 2
  nom   |  narx   | soni |   jami
--------+---------+------+-----------
 Daftar | 5000.00 |   12 |  60000.00
 Ruchka | 3000.00 |   40 | 120000.00
(2 rows)

jami ustuniga hech kim qiymat yozmadi - baza uni o'zi hisobladi. Va uni qo'lda o'zgartirib ham bo'lmaydi.

Bu nomuvofiqlikning oldini oladi: narx yangilansa, jami avtomatik qayta hisoblanadi.

Sxemani ko'rish #

Jadval qanday yaratilganini \d bilan tekshirish mumkin:

Natija

kutubxona=# \d kitob
                            Table "public.kitob"
   Column   |     Type      | Nullable |             Default
------------+---------------+----------+------------------------------
 id         | integer       | not null | generated always as identity
 sarlavha   | text          | not null |
 muallif_id | integer       | not null |
 nashr_yili | integer       |          |
 narx       | numeric(10,2) | not null |
Check constraints:
    "kitob_narx_check" CHECK (narx >= 0::numeric)
Foreign-key constraints:
    "kitob_muallif_id_fkey" FOREIGN KEY (muallif_id) REFERENCES muallif(id)

Amaliy topshiriq

kitob jadvaliga til ustunini DEFAULT 'uz' bilan qo'shing.
nashr_yili uchun cheklovni 1500-2030 oralig'iga o'zgartiring.
sahifa ga manfiy qiymat kiritishga urinib ko'ring.
Xato xabarida qaysi cheklov nomi chiqqanini yozing.
Cheklovga o'zingiz nom bering va xatoni qaytadan chaqiring.
Bir xil ISBN bilan ikkita kitob qo'shishga urinib ko'ring.
ON DELETE CASCADE bilan yangi jadval yarating va farqni ko'ring.
CASCADE qachon xavfli ekanini uch qatorda yozing.
Hisoblanadigan ustunga qo'lda qiymat yozishga urinib ko'ring.
O'z loyihangizdagi bitta jadvalga ikkita cheklov qo'shing.

Xulosa #

Cheklov ilovada emas, bazada turishi kerak - uni hech kim chetlab o'ta olmaydi.
NOT NULL - ustun bo'sh qolmasligini, DEFAULT - qiymat berilmasa nima yozilishini belgilaydi.
CHECK mantiqiy qoidani yozadi: narx >= 0, sahifa > 0.
UNIQUE takrorlanishni, PRIMARY KEY esa qatorni aniqlashni ta'minlaydi.
REFERENCES bog'langan qator haqiqatan mavjudligini kafolatlaydi.
Tashqi kalitda ON DELETE xatti-harakatini ochiq ko'rsating.
RESTRICT xavfsizroq standart; CASCADE jimgina zanjir bo'ylab o'chiradi.
Cheklovga o'zingiz nom bering - xato xabari tushunarli bo'ladi.
GENERATED ALWAYS AS (...) STORED ustunni boshqa ustunlardan hisoblaydi.
Cheklov ayni paytda hujjat ham: sxemani o'qigan odam qoidalarni ko'radi.

Keyingi bo'limda ma'lumotni o'zgartiramiz va PostgreSQL ning kuchli RETURNING imkoniyati bilan tanishamiz.

Jadval yaratish #

Cheklov ishlayotganini ko'rish #

Tashqi kalit va o'chirish xatti-harakati #

Hisoblanadigan ustunlar #

Sxemani ko'rish #

Xulosa #

Xatolik topdingizmi?