Jadvallar orasidagi bog'lanishlar

-- Yomon: hamma narsa bitta jadvalda
CREATE TABLE buyurtmalar (
    id            INT PRIMARY KEY,
    mijoz_ism     VARCHAR(120),
    mijoz_telefon VARCHAR(20),
    mijoz_manzil  VARCHAR(255),
    mahsulot      VARCHAR(160),
    narx          DECIMAL(12,2)
);

CREATE TABLE mijozlar (
    id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ism     VARCHAR(120) NOT NULL,
    telefon VARCHAR(20),
    manzil  VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE buyurtmalar (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mijoz_id  INT UNSIGNED NOT NULL,
    mahsulot  VARCHAR(160) NOT NULL,
    narx      DECIMAL(12,2) NOT NULL,

    FOREIGN KEY (mijoz_id) REFERENCES mijozlar (id)
) ENGINE=InnoDB;

CREATE TABLE guruhlar (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nomi VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE talabalar (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guruh_id INT UNSIGNED NOT NULL,
    ism      VARCHAR(120) NOT NULL,

    CONSTRAINT fk_talaba_guruh
        FOREIGN KEY (guruh_id) REFERENCES guruhlar (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE fanlar (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nomi VARCHAR(120) NOT NULL
) ENGINE=InnoDB;

-- Oraliq (bog'lovchi) jadval
CREATE TABLE talaba_fan (
    talaba_id INT UNSIGNED NOT NULL,
    fan_id    INT UNSIGNED NOT NULL,
    baho      TINYINT UNSIGNED,
    sana      DATE,

    PRIMARY KEY (talaba_id, fan_id),

    FOREIGN KEY (talaba_id) REFERENCES talabalar (id) ON DELETE CASCADE,
    FOREIGN KEY (fan_id)    REFERENCES fanlar (id)    ON DELETE CASCADE
) ENGINE=InnoDB;

-- Foydalanish
INSERT INTO talaba_fan (talaba_id, fan_id, baho, sana) VALUES
    (1, 1, 5, '2026-06-15'),
    (1, 2, 4, '2026-06-18'),
    (2, 1, 3, '2026-06-15');

CREATE TABLE foydalanuvchilar (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL UNIQUE,
    parol VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE profillar (
    foydalanuvchi_id INT UNSIGNED PRIMARY KEY,
    tugilgan_sana    DATE,
    bio              TEXT,
    avatar           VARCHAR(255),

    FOREIGN KEY (foydalanuvchi_id)
        REFERENCES foydalanuvchilar (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Guruh o'chsa, talabalar ham o'chsin
FOREIGN KEY (guruh_id) REFERENCES guruhlar (id)
    ON DELETE CASCADE

-- Guruh o'chsa, talabalar guruhsiz qolsin
FOREIGN KEY (guruh_id) REFERENCES guruhlar (id)
    ON DELETE SET NULL      -- ustun NULL ga ruxsat berishi kerak

-- Talabasi bor guruhni o'chirib bo'lmasin
FOREIGN KEY (guruh_id) REFERENCES guruhlar (id)
    ON DELETE RESTRICT

-- Mavjud bo'lmagan guruhga talaba qo'shishga urinish
INSERT INTO talabalar (guruh_id, ism) VALUES (999, 'Test');

-- Talabasi bor guruhni o'chirishga urinish (RESTRICT bilan)
DELETE FROM guruhlar WHERE id = 1;

-- Ichki so'rov bilan (sekin)
SELECT
    ism,
    (SELECT nomi FROM guruhlar WHERE id = talabalar.guruh_id) AS guruh
FROM talabalar;

-- JOIN bilan (to'g'ri usul - keyingi bo'lim)
SELECT t.ism, g.nomi AS guruh
FROM talabalar t
JOIN guruhlar g ON g.id = t.guruh_id;

CREATE DATABASE IF NOT EXISTS dokon
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dokon;

CREATE TABLE kategoriyalar (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nomi VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE mahsulotlar (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    kategoriya_id INT UNSIGNED NOT NULL,
    nomi         VARCHAR(160) NOT NULL,
    narx         DECIMAL(12,2) NOT NULL,
    ombor        INT UNSIGNED NOT NULL DEFAULT 0,

    CONSTRAINT fk_mahsulot_kategoriya
        FOREIGN KEY (kategoriya_id) REFERENCES kategoriyalar (id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    KEY idx_kategoriya (kategoriya_id)
) ENGINE=InnoDB;

CREATE TABLE mijozlar (
    id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ism     VARCHAR(120) NOT NULL,
    email   VARCHAR(190) NOT NULL UNIQUE,
    telefon VARCHAR(20)
) ENGINE=InnoDB;

CREATE TABLE buyurtmalar (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mijoz_id   INT UNSIGNED NOT NULL,
    holat      ENUM('yangi','tolangan','yetkazildi','bekor') NOT NULL DEFAULT 'yangi',
    yaratilgan TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_buyurtma_mijoz
        FOREIGN KEY (mijoz_id) REFERENCES mijozlar (id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    KEY idx_mijoz (mijoz_id),
    KEY idx_holat (holat, yaratilgan)
) ENGINE=InnoDB;

-- Ko'p-ko'p: bitta buyurtmada ko'p mahsulot
CREATE TABLE buyurtma_elementlari (
    buyurtma_id  INT UNSIGNED NOT NULL,
    mahsulot_id  INT UNSIGNED NOT NULL,
    soni         SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    narx         DECIMAL(12,2) NOT NULL,   -- sotib olish paytidagi narx

    PRIMARY KEY (buyurtma_id, mahsulot_id),

    FOREIGN KEY (buyurtma_id) REFERENCES buyurtmalar (id) ON DELETE CASCADE,
    FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar (id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Jadvalning to'liq ta'rifi
SHOW CREATE TABLE buyurtmalar\G

-- Barcha tashqi kalitlar
SELECT
    TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'dokon';