09. Constraints -- Data Integrity Rules

Level: Beginner

ℹ️ What You'll Learn

• PRIMARY KEY -- unique identifier for each row
• NOT NULL -- column must have value
• UNIQUE -- no duplicate values
• DEFAULT -- automatic default value
• CHECK -- validate values (ranges, patterns)
• Adding constraints to existing tables
• Constraints in data model design
• Common constraint mistakes

Tables are empty until you define rules. Constraints enforce data quality at database level. This article explains every constraint type.

Why Constraints Matter for Backend Developers

Constraints prevent bad data before it enters database:

-- WITHOUT constraints (bad data enters)
StudentId: NULL, 123, 123, 456  <- Duplicate and NULL IDs!
RollNumber: "ABC", "10-A", 102  <- Text mixed with numbers!
Email: NULL, "ravi", NULL       <- Duplicates, invalid format!
Age: 25, -5, 150                <- Invalid ages!

-- WITH constraints (data stays clean)
StudentId: 1, 2, 3, 4           <- Always unique, never NULL
RollNumber: 101, 102, 103       <- Always numbers
Email: Always unique, valid format
Age: Always 5-25 (valid range)

Constraints enforce quality in database, not in code.

PRIMARY KEY Constraint

Unique identifier. No duplicates, no NULL.

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100)
);

Properties:

• Must be unique (no 2 students with same ID)
• Cannot be NULL
• One per table
• Automatically indexed

Why:

• Identifies each row uniquely
• Required for relationships (Foreign Keys)
• Enables efficient lookups

NOT NULL Constraint

Column must have value. Cannot be empty/NULL.

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,        -- Required
    RollNumber INT NOT NULL,            -- Required
    DateOfBirth DATE,                   -- Optional
    ParentPhone VARCHAR(15)             -- Optional
);

NOT NULL columns: Name, RollNumber (must provide always). Optional columns: DateOfBirth, ParentPhone (can be NULL).

Try inserting without Name:

INSERT INTO Student (RollNumber) VALUES (101);
-- Error: Column 'Name' cannot be null

UNIQUE Constraint

No duplicate values (but NULL allowed).

CREATE TABLE Teacher
(
    TeacherId INT PRIMARY KEY,
    Name NVARCHAR(100),
    EmployeeCode VARCHAR(20) UNIQUE,  -- No two teachers same code
    Email NVARCHAR(100) UNIQUE         -- No duplicate emails
);

Try adding duplicate:

INSERT INTO Teacher (TeacherId, Name, EmployeeCode) 
VALUES (1, 'Dr. Mehta', 'EMP001');

INSERT INTO Teacher (TeacherId, Name, EmployeeCode) 
VALUES (2, 'Mrs. Rao', 'EMP001');
-- Error: Violation of UNIQUE KEY constraint

Note: Multiple NULL values allowed:

INSERT INTO Teacher (TeacherId, Name, EmployeeCode) 
VALUES (3, 'Mr. Sharma', NULL);

INSERT INTO Teacher (TeacherId, Name, EmployeeCode) 
VALUES (4, 'Ms. Khan', NULL);
-- Both allowed (NULL != NULL in SQL)

DEFAULT Constraint

Automatic value if not provided.

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    ClassName VARCHAR(10) DEFAULT '10-A',
    Status VARCHAR(20) DEFAULT 'Active',
    JoiningDate DATE DEFAULT GETDATE()  -- Today's date
);

Insert without Status:

INSERT INTO Student (Name, ClassName) 
VALUES ('Ravi Kumar', '10-B');

-- Result:
-- StudentId = 1 (IDENTITY)
-- Name = 'Ravi Kumar'
-- ClassName = '10-B'
-- Status = 'Active' (DEFAULT)
-- JoiningDate = 2024-01-15 (today)

Why useful: Provides sensible defaults, reduces NULL values.

CHECK Constraint

Validate values match condition.

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY,
    Name NVARCHAR(100),
    Age INT CHECK (Age >= 5 AND Age <= 25),     -- Valid age range
    GPA DECIMAL(3,2) CHECK (GPA >= 0 AND GPA <= 4),  -- 0 to 4
    Status VARCHAR(20) CHECK (Status IN ('Active', 'Inactive', 'Graduated'))  -- Only these values
);

Try invalid data:

INSERT INTO Student (StudentId, Name, Age) 
VALUES (1, 'Ravi', 150);
-- Error: The INSERT, UPDATE, or DELETE statement conflicted with a CHECK constraint

INSERT INTO Student (StudentId, Name, Status) 
VALUES (2, 'Priya', 'Suspended');
-- Error: Conflicts with CHECK constraint (only Active/Inactive/Graduated allowed)

Valid insert:

INSERT INTO Student (StudentId, Name, Age, GPA, Status) 
VALUES (3, 'Arjun', 16, 3.8, 'Active');
-- Success: All CHECK constraints pass

Adding Constraints to Existing Tables

Add PRIMARY KEY:

ALTER TABLE Student
ADD PRIMARY KEY (StudentId);

Add NOT NULL:

ALTER TABLE Student
ALTER COLUMN Name NVARCHAR(100) NOT NULL;

Add UNIQUE:

ALTER TABLE Teacher
ADD UNIQUE (EmployeeCode);

Add DEFAULT:

ALTER TABLE Student
ADD DEFAULT 'Active' FOR Status;

Add CHECK:

ALTER TABLE Student
ADD CHECK (Age >= 5 AND Age <= 25);

Example: Well-Constrained Table

CREATE TABLE Student
(
    -- Primary Key
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    
    -- Required fields (NOT NULL)
    Name NVARCHAR(100) NOT NULL,
    RollNumber INT NOT NULL,
    ClassName VARCHAR(10) NOT NULL,
    
    -- Unique fields
    Email NVARCHAR(100) UNIQUE,
    
    -- Optional fields
    DateOfBirth DATE,
    Address NVARCHAR(255),
    
    -- Default values
    Status VARCHAR(20) DEFAULT 'Active',
    JoiningDate DATE DEFAULT GETDATE(),
    
    -- Validation (CHECK)
    CHECK (ClassName IN ('10-A', '10-B', '10-C', '11-A', '11-B', '12-A', '12-B')),
    CHECK (Status IN ('Active', 'Inactive', 'Graduated'))
);

---

Common Constraint Mistakes

Mistake 1: Making all columns NOT NULL

Wrong:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    RollNumber INT NOT NULL,
    DateOfBirth DATE NOT NULL,      -- Too strict!
    ParentPhone VARCHAR(15) NOT NULL, -- Too strict!
    Address NVARCHAR(255) NOT NULL   -- Too strict!
);

Cannot insert without ALL information. Real-world data incomplete sometimes.

Result: Insert fails even for valid partial data.

Fix: Only NOT NULL truly required fields:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,           -- Required
    RollNumber INT NOT NULL,               -- Required
    DateOfBirth DATE,                      -- Optional
    ParentPhone VARCHAR(15),               -- Optional
    Address NVARCHAR(255)                  -- Optional
);

Mistake 2: UNIQUE constraint on nullable column

Wrong:

CREATE TABLE Student
(
    Email NVARCHAR(100) UNIQUE
    -- Problem: Multiple NULLs allowed, defeats uniqueness
);

Multiple students can have NULL email (NULL != NULL).

Result: Looks like duplicates allowed, confusing.

Fix: Combine with NOT NULL:

Email NVARCHAR(100) UNIQUE NOT NULL
-- Now enforces: Always unique, never NULL

Mistake 3: CHECK constraint too restrictive

Wrong:

CREATE TABLE Exam
(
    ExamDate DATE CHECK (ExamDate = CAST(GETDATE() AS DATE))
    -- Only today's date allowed!
);

Can only insert exams for today. Cannot schedule future exams.

Result: Table useless for real data.

Fix: Use realistic range:

ExamDate DATE CHECK (ExamDate >= '2024-01-01' AND ExamDate <= '2030-12-31')
-- Allows any date in reasonable range

Mistake 4: Forgetting to drop constraint before modifying column

Wrong:

ALTER TABLE Student
ALTER COLUMN Age INT;
-- Error: Cannot alter column if it has CHECK constraint

Result: Modification fails.

Fix: Drop constraint first:

ALTER TABLE Student
DROP CONSTRAINT CK_Age;  -- Drop the CHECK

ALTER TABLE Student
ALTER COLUMN Age INT;    -- Now succeeds

---

Best Practices for Constraints

1. Always have PRIMARY KEY -- Essential for data integrity
2. Use NOT NULL wisely -- Only truly required fields
3. Add UNIQUE for identifiers -- Email, code, no duplicates
4. Use DEFAULT for common values -- Reduces NULL
5. Use CHECK for enumerated values -- Status, type fields
6. Document constraints -- Comments explain why
7. Test constraint violations -- Ensure they work as intended

---

🎯 Q1: Why is PRIMARY KEY essential?

PRIMARY KEY uniquely identifies each row.

Without:

StudentId | Name
NULL      | Ravi Kumar
NULL      | Ravi Kumar    <- Duplicate!
123       | Priya Sharma
123       | Priya Sharma  <- Duplicate!

Cannot reliably find "the" student. Duplicates possible.

With PRIMARY KEY:

StudentId | Name
1         | Ravi Kumar
2         | Ravi Kumar    <- Different ID, OK
3         | Priya Sharma
4         | Priya Sharma  <- Different ID, OK

Each student unique. Foreign keys can reference them safely.

Rule: Every table needs PRIMARY KEY.

🎯 Q2: What's the difference between NOT NULL and UNIQUE?

NOT NULL:

• Prevents NULL (empty values)
• Multiple non-NULL duplicates allowed
• Name NVARCHAR(100) NOT NULL -> Can have 2 students named "Ravi"

UNIQUE:

• Prevents duplicates
• NULL allowed (multiple NULLs OK)
• Email NVARCHAR(100) UNIQUE -> Max one per email, but multiple NULLs OK

Combine for safety:

Email NVARCHAR(100) UNIQUE NOT NULL
-- Unique: no duplicates
-- Not NULL: always provided
-- Result: Every student has unique, non-empty email

🎯 Q3: When should I use DEFAULT constraint?

Good use cases:

Status VARCHAR(20) DEFAULT 'Active'      -- Most students active
IsActive BIT DEFAULT 1                   -- Most records active
JoiningDate DATE DEFAULT GETDATE()       -- Today's date usually
CreatedAt DATETIME2 DEFAULT GETDATE()    -- Audit timestamp

Bad use cases:

Name NVARCHAR(100) DEFAULT 'Unknown'     -- No default for names!
StudentId INT DEFAULT 1                  -- Don't default IDs!
Email DEFAULT 'no-email@example.com'     -- Never default email!

Use DEFAULT for:

• Status/state fields (Active, Inactive)
• Timestamps (creation time)
• Counts (default 0)

Don't DEFAULT for:

• Names, unique identifiers, email

🎯 Q4: How do CHECK constraints prevent bad data?

Without CHECK:

INSERT INTO Student (StudentId, Status) VALUES (1, 'Blah');
-- Succeeds! Now code has to handle 'Blah' status

With CHECK:

Status VARCHAR(20) CHECK (Status IN ('Active', 'Inactive', 'Graduated'))

INSERT INTO Student (StudentId, Status) VALUES (1, 'Blah');
-- Error: Conflicts with CHECK constraint
-- Forced to use valid status

CHECK prevents garbage data at source.

Real benefit: No handling invalid statuses in backend code.

🎯 Q5: Can I add constraints to existing table?

Yes, using ALTER TABLE:

-- Add NOT NULL
ALTER TABLE Student
ALTER COLUMN Name NVARCHAR(100) NOT NULL;

-- Add UNIQUE
ALTER TABLE Teacher
ADD UNIQUE (EmployeeCode);

-- Add CHECK
ALTER TABLE Student
ADD CHECK (Age >= 5 AND Age <= 25);

-- Add DEFAULT
ALTER TABLE Student
ADD DEFAULT 'Active' FOR Status;

But: If existing data violates constraint, ALTER fails.

Solution:

-- First fix existing data
UPDATE Student SET Status = 'Active' WHERE Status IS NULL;

-- Then add constraint
ALTER TABLE Student
ALTER COLUMN Status NVARCHAR(20) NOT NULL;

🎯 Q6: What happens when constraint is violated?

Example:

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY,
    Email NVARCHAR(100) UNIQUE NOT NULL,
    Age INT CHECK (Age >= 5 AND Age <= 25)
);

-- Violation 1: Duplicate email
INSERT INTO Student VALUES (1, 'ravi@example.com', 16);
INSERT INTO Student VALUES (2, 'ravi@example.com', 17);
-- Error: Violation of UNIQUE KEY constraint

-- Violation 2: NULL email
INSERT INTO Student VALUES (3, NULL, 18);
-- Error: Cannot insert NULL into NOT NULL column

-- Violation 3: Age out of range
INSERT INTO Student VALUES (4, 'new@example.com', 150);
-- Error: The INSERT conflicted with a CHECK constraint

Result: Data rejected. Table stays clean.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on SQL Server constraints and data integrity rules. Try these prompts:

• "Explain PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT with Student table examples"
• "What's the difference between NOT NULL and UNIQUE constraints?"
• "How do CHECK constraints prevent bad data? Show examples"
• "When should I use DEFAULT? Show good and bad examples"
• "Quiz me: ask 5 questions about constraints, data integrity, violations"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

10. Foreign Keys & Table Relationships ->