11. MySQL Part 2: UPDATE, ALTER, DELETE, Keys, Constraints and Users

11.6 Constraints: NOT NULL, DEFAULT, CHECK, AUTO_INCREMENT

By Ravindra Bagale · Last updated: 27 September 2026

Constraints (मर्यादा / नियम) mhanje database-level niyam – chukicha data table madhe yetach nahi, app madhe bug asla tari.

CREATE TABLE payments (
  payment_id INT AUTO_INCREMENT PRIMARY KEY,          -- 1, 2, 3 ... automatically
  student_id INT NOT NULL,                            -- must have a value
  amount     DECIMAL(10,2) NOT NULL CHECK (amount > 0),
  mode       VARCHAR(10) NOT NULL DEFAULT 'UPI'
             CHECK (mode IN ('UPI','Card','Cash','NetBanking')),
  paid_on    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

INSERT INTO payments (student_id, amount) VALUES (1, 5000);     -- mode=UPI, paid_on=now
INSERT INTO payments (student_id, amount) VALUES (1, -10);      -- rejected by CHECK
INSERT INTO payments (amount) VALUES (100);                     -- rejected: student_id NOT NULL
ALTER TABLE payments AUTO_INCREMENT = 1001;                     -- next id starts at 1001