Cheklovlar

CREATE TABLE xodimlar (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    email     VARCHAR(100) NOT NULL UNIQUE,
    ism       VARCHAR(100) NOT NULL,
    yosh      TINYINT UNSIGNED NOT NULL,
    maosh     DECIMAL(10,2) NOT NULL DEFAULT 0,
    holat     ENUM('faol', 'tatil', 'ishdan_ketgan') NOT NULL DEFAULT 'faol',
    yaratilgan DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_yosh   CHECK (yosh BETWEEN 18 AND 70),
    CONSTRAINT chk_maosh  CHECK (maosh >= 0),
    CONSTRAINT chk_email  CHECK (email LIKE '%@%.%')
);

INSERT INTO xodimlar (email, ism, yosh, maosh) VALUES
    ('[email protected]', 'Husanboy', 25, 8000000),
    ('[email protected]',   'Malika',   30, 9500000);

SELECT id, ism, yosh, maosh, holat FROM xodimlar ORDER BY id;

-- Majburiy ustunni tashlab ketish
INSERT INTO xodimlar (email, yosh) VALUES ('[email protected]', 25);

ERROR 1364 (HY000): Field 'ism' doesn't have a default value

-- DEFAULT bor ustunlarni tashlab ketish mumkin
INSERT INTO xodimlar (email, ism, yosh) VALUES ('[email protected]', 'Kamola', 22);

SELECT ism, maosh, holat FROM xodimlar WHERE ism = 'Kamola';

INSERT INTO xodimlar (email, ism, yosh)
VALUES ('[email protected]', 'Boshqa odam', 40);

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'email'

-- Kompozit UNIQUE: bir kunda bir xodim bir marta belgilanadi
CREATE TABLE davomat (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    xodim_id  INT NOT NULL,
    sana      DATE NOT NULL,
    kelgan    TIME NOT NULL,
    CONSTRAINT uq_xodim_sana UNIQUE (xodim_id, sana),
    FOREIGN KEY (xodim_id) REFERENCES xodimlar(id)
);

INSERT INTO davomat (xodim_id, sana, kelgan) VALUES
    (1, '2026-09-10', '09:00:00'),
    (2, '2026-09-10', '08:45:00'),
    (1, '2026-09-11', '09:05:00');

SELECT xodim_id, sana, kelgan FROM davomat ORDER BY id;

-- Bir kunda ikkinchi marta belgilash mumkin emas
INSERT INTO davomat (xodim_id, sana, kelgan) VALUES (1, '2026-09-10', '14:00:00');

ERROR 1062 (23000): Duplicate entry '1-2026-09-10' for key 'uq_xodim_sana'

INSERT INTO xodimlar (email, ism, yosh) VALUES ('[email protected]', 'Yosh', 15);

ERROR 4025 (23000): CONSTRAINT `chk_yosh` failed for `maktab`.`xodimlar`

INSERT INTO xodimlar (email, ism, yosh, maosh)
VALUES ('[email protected]', 'Test', 30, -100);

ERROR 4025 (23000): CONSTRAINT `chk_maosh` failed for `maktab`.`xodimlar`

INSERT INTO xodimlar (email, ism, yosh) VALUES ('notogri', 'Test', 30);

ERROR 4025 (23000): CONSTRAINT `chk_email` failed for `maktab`.`xodimlar`

-- Cheklovlar sxemada ko'rinadi
SELECT CONSTRAINT_NAME AS nomi, CHECK_CLAUSE AS shart
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'xodimlar'
ORDER BY BINARY CONSTRAINT_NAME;

CREATE TABLE nomsiz (
    id  INT PRIMARY KEY,
    yosh INT CHECK (yosh > 0)
);

SELECT CONSTRAINT_NAME AS nomi
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'nomsiz';

CREATE TABLE mahsulotlar (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    nom  VARCHAR(100) NOT NULL,
    narx DECIMAL(10,2) NOT NULL
);

INSERT INTO mahsulotlar (nom, narx) VALUES
    ('Noutbuk', 8000000),
    ('Sichqoncha', 150000),
    ('Buzuq yozuv', -500);

-- Mavjud ma'lumot cheklovni buzsa, qo'shib bo'lmaydi
ALTER TABLE mahsulotlar ADD CONSTRAINT chk_narx CHECK (narx > 0);

ERROR 4025 (23000): CONSTRAINT `chk_narx` failed for `maktab`.`mahsulotlar`

-- Avval ma'lumotni tozalash kerak
SELECT id, nom, narx FROM mahsulotlar WHERE narx <= 0;

DELETE FROM mahsulotlar WHERE narx <= 0;

ALTER TABLE mahsulotlar ADD CONSTRAINT chk_narx CHECK (narx > 0);

SELECT CONSTRAINT_NAME AS nomi
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'mahsulotlar';

SELECT
    tc.CONSTRAINT_NAME AS nomi,
    tc.CONSTRAINT_TYPE AS turi
FROM information_schema.TABLE_CONSTRAINTS tc
WHERE tc.TABLE_SCHEMA = DATABASE() AND tc.TABLE_NAME = 'xodimlar'
ORDER BY BINARY tc.CONSTRAINT_NAME;