MySQL + SQL · Lesson 16

Keys and Constraints in DBMS

What are Keys?

Keys are one or more columns in a table that are used to uniquely identify each row, establish relationships between tables, and ensure data integrity. Keys are the backbone of a relational database design.

Without keys, a database cannot:

Distinguish between two students named "Aarav Sharma".
Link fee records to the correct student.
Prevent duplicate or incorrect data entry.

Types of Keys in DBMS

Key Type	Definition	School Example
Primary Key	Uniquely identifies each row. Cannot be NULL or duplicate.	roll_no in students table — each student has unique roll number
Foreign Key	A column that refers to the PRIMARY KEY of another table. Maintains referential integrity.	roll_no in fee_records refers to roll_no in students table
Candidate Key	Any column that can become a primary key — unique and not null.	roll_no, aadhar_no, email — all can uniquely identify a student
Super Key	Any combination of columns that uniquely identifies a row (includes candidate keys + extra columns).	{roll_no}, {roll_no, name}, {aadhar_no, city} — all are super keys
Alternate Key	Candidate keys not chosen as primary key.	If roll_no is primary key, then aadhar_no and email are alternate keys
Composite Key	Primary key made of TWO or more columns together.	In exam_results: {roll_no + subject} together identify one unique mark entry
Unique Key	Ensures uniqueness like primary key but allows ONE NULL value.	email in students — unique but student may not have email yet

What are Constraints?

Constraints are rules applied to columns that automatically prevent invalid, incorrect or inconsistent data from entering the database. They enforce data integrity without any extra programming.

Constraints are defined while creating a table using CREATE TABLE or added later using ALTER TABLE.

Types of Constraints in MySQL

Constraint	Meaning	Example
NOT NULL	Column cannot be left empty — value is mandatory	Student name must always be entered
UNIQUE	All values in column must be different — duplicates not allowed	Email ID must be different for every student
PRIMARY KEY	Unique + NOT NULL — main identifier of each row	roll_no: no two students can have same roll, and it must exist
FOREIGN KEY	Links to PRIMARY KEY of another table — prevents orphan records	fee_records.roll_no must exist in students.roll_no
CHECK	Validates data against a condition — rejects invalid values	marks must be between 0 and 100; age must be > 5
DEFAULT	Sets automatic value if no value is provided during INSERT	attendance defaults to 'P' (present) if not specified
AUTO_INCREMENT	Automatically generates next number — no manual entry needed	fee_id: 1, 2, 3... automatically assigned

SQL Examples with Output

Complete School Database with Keys and Constraints

CREATE DATABASE school_db;
USE school_db;

-- PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT
CREATE TABLE students (
  roll_no INT PRIMARY KEY,                          -- PRIMARY KEY: unique + not null
  name VARCHAR(80) NOT NULL,                        -- NOT NULL: name required
  email VARCHAR(100) UNIQUE,                        -- UNIQUE: no duplicate emails
  gender CHAR(1) DEFAULT 'M',                       -- DEFAULT: M if not specified
  city VARCHAR(50) NOT NULL
);

-- FOREIGN KEY, CHECK, AUTO_INCREMENT
CREATE TABLE marks (
  mark_id INT AUTO_INCREMENT PRIMARY KEY,           -- AUTO_INCREMENT: auto ID
  roll_no INT NOT NULL,
  subject VARCHAR(40) NOT NULL,
  marks INT CHECK (marks BETWEEN 0 AND 100),        -- CHECK: marks must be 0-100
  FOREIGN KEY (roll_no) REFERENCES students(roll_no) -- FOREIGN KEY: must exist in students
    ON DELETE CASCADE                               -- if student deleted, marks deleted too
);

-- COMPOSITE KEY example
CREATE TABLE exam_schedule (
  roll_no INT,
  subject VARCHAR(40),
  exam_date DATE,
  room_no INT,
  PRIMARY KEY (roll_no, subject),                   -- COMPOSITE KEY: both together = unique
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

Insert Data – Constraint Validation in Action

-- Correct insert
INSERT INTO students VALUES (1, 'Aarav Sharma', 'aarav@email.com', 'M', 'Khurja');
INSERT INTO students VALUES (2, 'Priya Verma', 'priya@email.com', 'F', 'Aligarh');

-- This will FAIL — duplicate roll_no (PRIMARY KEY violation)
INSERT INTO students VALUES (1, 'Ravi Singh', 'ravi@email.com', 'M', 'Meerut');
-- ERROR 1062: Duplicate entry '1' for key 'PRIMARY'

-- This will FAIL — marks out of range (CHECK violation)
INSERT INTO marks (roll_no, subject, marks) VALUES (1, 'Math', 110);
-- ERROR 3819: Check constraint violated

-- This will FAIL — roll_no 99 doesn't exist in students (FOREIGN KEY violation)
INSERT INTO marks (roll_no, subject, marks) VALUES (99, 'Math', 85);
-- ERROR 1452: Cannot add or update a child row: foreign key constraint fails

-- DEFAULT test — gender not provided, will be 'M' automatically
INSERT INTO students (roll_no, name, email, city) VALUES (3, 'Neha Gupta', 'neha@email.com', 'Agra');
SELECT * FROM students WHERE roll_no = 3;

Adding Constraints Later with ALTER TABLE

-- Add NOT NULL constraint to existing column
ALTER TABLE students MODIFY city VARCHAR(50) NOT NULL;

-- Add UNIQUE constraint to existing column
ALTER TABLE students ADD CONSTRAINT uq_email UNIQUE (email);

-- Add CHECK constraint
ALTER TABLE marks ADD CONSTRAINT chk_marks CHECK (marks >= 0 AND marks <= 100);

-- Add FOREIGN KEY to existing table
ALTER TABLE marks ADD CONSTRAINT fk_roll
  FOREIGN KEY (roll_no) REFERENCES students(roll_no);

-- Remove a constraint
ALTER TABLE marks DROP CONSTRAINT chk_marks;

Common Mistakes

Forgetting PRIMARY KEY: Table without primary key allows duplicate rows — data becomes unreliable.
Confusing UNIQUE with PRIMARY KEY: UNIQUE allows one NULL; PRIMARY KEY never allows NULL. A table can have many UNIQUE columns but only one PRIMARY KEY.
FOREIGN KEY mismatch: Data types of foreign key and referenced primary key must match exactly (both INT, or both VARCHAR with same length).
CHECK constraint in old MySQL: MySQL 5.7 and earlier accepted CHECK syntax but did NOT enforce it. CHECK is enforced only from MySQL 8.0+.
Deleting parent record with child data: Deleting a student who has fee records → FOREIGN KEY error unless ON DELETE CASCADE is set.
Not naming constraints: Always name important constraints (CONSTRAINT fk_roll FOREIGN KEY...) so they can be dropped later easily.

Practice Tasks

Create a library_db with books table (book_id PRIMARY KEY, title NOT NULL, isbn UNIQUE, available_copies CHECK >= 0) and issued_books table with FOREIGN KEY to both books and students.
Try inserting a row that violates each constraint (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) and write the exact error message you get.
Write a table showing all 7 types of keys with 1 example each from a hospital database (patients, doctors, appointments).
Viva Q: "What is the difference between PRIMARY KEY and UNIQUE KEY?" — Answer in 4 points.

Summary

Keys identify rows and link tables — Primary, Foreign, Candidate, Super, Alternate, Composite, Unique.
Constraints enforce data rules automatically — NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
PRIMARY KEY = Unique + NOT NULL. UNIQUE = Unique + allows one NULL.
FOREIGN KEY ensures referential integrity — child record must have matching parent.
CHECK is enforced from MySQL 8.0+. AUTO_INCREMENT generates automatic sequential IDs.
Always design keys and constraints at table creation time — they save enormous debugging time later.

Keys क्या हैं?

Keys table में एक या अधिक columns होते हैं जो हर row को uniquely identify करते हैं, tables के बीच relationships बनाते हैं, और data integrity ensure करते हैं। Keys relational database design की backbone हैं।

Keys के बिना database:

"Aarav Sharma" नाम के दो students में अंतर नहीं कर सकता।
Fee records को correct student से link नहीं कर सकता।
Duplicate या incorrect data रोक नहीं सकता।

Keys के प्रकार

Key Type	Definition	School Example
Primary Key	हर row को uniquely identify करे। NULL या duplicate नहीं हो सकता।	students table में roll_no — हर student का अलग roll number
Foreign Key	दूसरी table के PRIMARY KEY को refer करे। Referential integrity maintain करे।	fee_records.roll_no → students.roll_no
Candidate Key	कोई भी column जो primary key बन सकता हो — unique और not null।	roll_no, aadhar_no, email — तीनों student को identify कर सकते हैं
Super Key	Columns का कोई भी combination जो row uniquely identify करे।	{roll_no}, {roll_no, name}, {aadhar_no, city}
Alternate Key	Candidate keys जो primary key नहीं चुने गए।	roll_no primary key है, तो aadhar_no और email alternate keys हैं
Composite Key	दो या अधिक columns मिलकर primary key बनाएं।	exam_results में {roll_no + subject} = एक unique entry
Unique Key	Uniqueness ensure करे लेकिन एक NULL allow करे।	students.email — unique, लेकिन email न हो तो NULL भी चलेगा

Constraints क्या हैं?

Constraints वे rules हैं जो columns पर apply होते हैं और automatically invalid, incorrect या inconsistent data को database में enter होने से रोकते हैं। ये data integrity enforce करते हैं बिना extra programming के।

Constraints के प्रकार

Constraint	मतलब	Example
NOT NULL	Column खाली नहीं रह सकता — value ज़रूरी है	Student का नाम mandatory है
UNIQUE	सभी values different होनी चाहिए	Email ID हर student की अलग
PRIMARY KEY	Unique + NOT NULL — हर row का main identifier	roll_no: duplicate नहीं, NULL नहीं
FOREIGN KEY	दूसरी table के PRIMARY KEY से link — orphan records नहीं	fee_records.roll_no students में exist करना चाहिए
CHECK	Condition के against validate करे	marks 0 से 100 के बीच होने चाहिए
DEFAULT	INSERT पर value न दो तो automatic value set हो	attendance default 'P'
AUTO_INCREMENT	अपने आप अगला number generate हो	fee_id: 1, 2, 3... automatically

SQL Examples with Output

CREATE DATABASE school_db;
USE school_db;

CREATE TABLE students (
  roll_no INT PRIMARY KEY,           -- PRIMARY KEY
  name VARCHAR(80) NOT NULL,         -- NOT NULL
  email VARCHAR(100) UNIQUE,         -- UNIQUE
  gender CHAR(1) DEFAULT 'M',        -- DEFAULT
  city VARCHAR(50) NOT NULL
);

CREATE TABLE marks (
  mark_id INT AUTO_INCREMENT PRIMARY KEY,  -- AUTO_INCREMENT
  roll_no INT NOT NULL,
  subject VARCHAR(40) NOT NULL,
  marks INT CHECK (marks BETWEEN 0 AND 100),  -- CHECK
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)  -- FOREIGN KEY
);

Constraint violations देखें

-- FAIL: duplicate roll_no (PRIMARY KEY violation)
INSERT INTO students VALUES (1, 'Ravi Singh', 'ravi@email.com', 'M', 'Meerut');
-- ERROR 1062: Duplicate entry '1' for key 'PRIMARY'

-- FAIL: marks 110 (CHECK violation)  
INSERT INTO marks (roll_no, subject, marks) VALUES (1, 'Math', 110);
-- ERROR 3819: Check constraint violated

-- FAIL: roll_no 99 students में नहीं है (FOREIGN KEY violation)
INSERT INTO marks (roll_no, subject, marks) VALUES (99, 'Math', 85);
-- ERROR 1452: foreign key constraint fails

हर गलत entry पर MySQL automatically ERROR देता है — data safe रहता है।

Common Mistakes

PRIMARY KEY भूल जाना: Primary key के बिना table duplicate rows allow करती है।
UNIQUE और PRIMARY KEY confuse करना: UNIQUE एक NULL allow करता है; PRIMARY KEY कभी NULL नहीं। एक table में कई UNIQUE columns हो सकते हैं लेकिन PRIMARY KEY सिर्फ एक।
FOREIGN KEY data type mismatch: Foreign key और referenced primary key का data type exactly same होना चाहिए।
MySQL 5.7 में CHECK: MySQL 5.7 में CHECK accept होता है लेकिन enforce नहीं होता। MySQL 8.0+ में ही enforce होता है।
Parent record delete करना: Student को delete करो जिसके fee records हैं → FOREIGN KEY error। ON DELETE CASCADE use करें।

Practice Tasks

library_db बनाएं: books (book_id PRIMARY KEY, title NOT NULL, isbn UNIQUE, copies CHECK >= 0) और issued_books FOREIGN KEY के साथ।
हर constraint को violate करने वाली INSERT query लिखें और exact error message note करें।
Hospital database (patients, doctors, appointments) के लिए सभी 7 keys के examples लिखें।
Viva: "PRIMARY KEY और UNIQUE KEY में क्या अंतर है?" — 4 points में answer।

Summary

Keys rows identify करते हैं और tables link करते हैं — Primary, Foreign, Candidate, Super, Alternate, Composite, Unique।
Constraints data rules automatically enforce करते हैं — NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT।
PRIMARY KEY = Unique + NOT NULL। UNIQUE = Unique + एक NULL allow।
FOREIGN KEY referential integrity ensure करता है।
CHECK MySQL 8.0+ में enforce होता है। AUTO_INCREMENT automatic sequential IDs देता है।