Integrity & Constraints

Constraints enforce strict rules at the storage level, protecting database tables against erratic or unvetted insert statements.

---

1. Row Evaluation Rules (CHECK)

CREATE DATABASE country;
USE country;

CREATE TABLE city (
    id INT PRIMARY KEY,
    city VARCHAR(20),
    age INT,
    CONSTRAINT check_rules CHECK (age >= 18 AND city = "delhi")
);

-- Compliant entry: Added successfully
INSERT INTO city (id, city, age) VALUES (1, "delhi", 21);

-- Non-compliant entry: Violates check constraint logic, rejected by engine
INSERT INTO city (id, city, age) VALUES (2, "mumbai", 17);

2. Automatic Fallback Values (DEFAULT)

CREATE DATABASE xyz_company;
USE xyz_company;

CREATE TABLE employee_info (
    id INT,
    name VARCHAR(20),
    salary INT DEFAULT 25000
);

-- Data insertion missing an explicit 'salary' column argument
INSERT INTO employee_info (id, name) VALUES (01, "arun");

-- Verification query showing default applied value
SELECT * FROM employee_info;