Tashqi kalitlar va referensial butunlik

CREATE TABLE mualliflar (
    id  INT PRIMARY KEY AUTO_INCREMENT,
    ism VARCHAR(100) NOT NULL
);

-- Tashqi kalitsiz jadval
CREATE TABLE kitoblar_himoyasiz (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    sarlavha   VARCHAR(200) NOT NULL,
    muallif_id INT NOT NULL
);

INSERT INTO mualliflar (ism) VALUES ('Abdulla Qodiriy'), ('Cho''lpon');

INSERT INTO kitoblar_himoyasiz (sarlavha, muallif_id) VALUES
    ('O''tkan kunlar', 1),
    ('Kecha va kunduz', 2),
    ('Sirli kitob', 999);          -- 999-muallif MAVJUD EMAS

SELECT k.sarlavha, k.muallif_id, COALESCE(m.ism, '(YETIM)') AS muallif
FROM kitoblar_himoyasiz k
LEFT JOIN mualliflar m ON m.id = k.muallif_id
ORDER BY k.id;

CREATE TABLE kitoblar (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    sarlavha   VARCHAR(200) NOT NULL,
    muallif_id INT NOT NULL,
    CONSTRAINT fk_kitob_muallif
        FOREIGN KEY (muallif_id) REFERENCES mualliflar(id)
);

INSERT INTO kitoblar (sarlavha, muallif_id) VALUES
    ('O''tkan kunlar', 1),
    ('Mehrobdan chayon', 1),
    ('Kecha va kunduz', 2);

SELECT m.ism, k.sarlavha
FROM kitoblar k
JOIN mualliflar m ON m.id = k.muallif_id
ORDER BY k.id;

-- Mavjud bo'lmagan muallif rad etiladi
INSERT INTO kitoblar (sarlavha, muallif_id) VALUES ('Sirli kitob', 999);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`maktab`.`kitoblar`, CONSTRAINT `fk_kitob_muallif`
FOREIGN KEY (`muallif_id`) REFERENCES `mualliflar` (`id`))

-- Kitobi bor muallifni o'chirib bo'lmaydi
DELETE FROM mualliflar WHERE id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`maktab`.`kitoblar`, CONSTRAINT `fk_kitob_muallif`
FOREIGN KEY (`muallif_id`) REFERENCES `mualliflar` (`id`))

CREATE TABLE buyurtmalar (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    mijoz  VARCHAR(100) NOT NULL
);

CREATE TABLE buyurtma_qatorlari (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    buyurtma_id  INT NOT NULL,
    mahsulot     VARCHAR(100) NOT NULL,
    CONSTRAINT fk_qator_buyurtma
        FOREIGN KEY (buyurtma_id) REFERENCES buyurtmalar(id)
        ON DELETE CASCADE
);

INSERT INTO buyurtmalar (mijoz) VALUES ('Husanboy'), ('Malika');

INSERT INTO buyurtma_qatorlari (buyurtma_id, mahsulot) VALUES
    (1, 'Noutbuk'), (1, 'Sichqoncha'), (2, 'Klaviatura');

SELECT
    (SELECT COUNT(*) FROM buyurtmalar)        AS buyurtmalar,
    (SELECT COUNT(*) FROM buyurtma_qatorlari) AS qatorlar;

-- Buyurtmani o'chirsak, uning qatorlari ham o'chadi
DELETE FROM buyurtmalar WHERE id = 1;

SELECT
    (SELECT COUNT(*) FROM buyurtmalar)        AS buyurtmalar,
    (SELECT COUNT(*) FROM buyurtma_qatorlari) AS qatorlar;

CREATE TABLE bolimlar (
    id  INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(50) NOT NULL
);

CREATE TABLE xodimlar (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    ism      VARCHAR(100) NOT NULL,
    bolim_id INT NULL,
    CONSTRAINT fk_xodim_bolim
        FOREIGN KEY (bolim_id) REFERENCES bolimlar(id)
        ON DELETE SET NULL
);

INSERT INTO bolimlar (nom) VALUES ('IT'), ('Hisobot');

INSERT INTO xodimlar (ism, bolim_id) VALUES
    ('Husanboy', 1), ('Malika', 1), ('Kamola', 2);

-- Bo'limni o'chirsak, xodimlar QOLADI, lekin bo'limsiz
DELETE FROM bolimlar WHERE id = 1;

SELECT ism, COALESCE(CAST(bolim_id AS CHAR), '(bolimsiz)') AS bolim
FROM xodimlar
ORDER BY id;

SELECT
    CONSTRAINT_NAME AS cheklov,
    UPDATE_RULE     AS yangilashda,
    DELETE_RULE     AS ochirishda
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
ORDER BY BINARY CONSTRAINT_NAME;

CREATE TABLE mijozlar (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    ism        VARCHAR(100) NOT NULL,
    ochirilgan DATETIME NULL DEFAULT NULL
);

CREATE TABLE buyurtmalar_yumshoq (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    mijoz_id INT NOT NULL,
    summa    DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id)
);

INSERT INTO mijozlar (ism) VALUES ('Husanboy'), ('Malika');

INSERT INTO buyurtmalar_yumshoq (mijoz_id, summa) VALUES
    (1, 8000000), (1, 150000), (2, 300000);

-- O'chirish o'rniga belgilash
UPDATE mijozlar SET ochirilgan = '2026-09-10 12:00:00' WHERE id = 1;

-- Faol mijozlar
SELECT ism FROM mijozlar WHERE ochirilgan IS NULL ORDER BY id;

-- Buyurtmalar saqlanib qoldi - hisobot to'g'ri ishlaydi
SELECT m.ism, COUNT(b.id) AS buyurtmalar, SUM(b.summa) AS jami
FROM mijozlar m
JOIN buyurtmalar_yumshoq b ON b.mijoz_id = m.id
GROUP BY m.id, m.ism
ORDER BY m.id;

SELECT k.id, k.sarlavha, k.muallif_id
FROM kitoblar_himoyasiz k
LEFT JOIN mualliflar m ON m.id = k.muallif_id
WHERE m.id IS NULL
ORDER BY k.id;