What is the NOT NULL constraint in SQL?

NOT NULL ensures a column cannot contain NULL values. It is used to enforce mandatory fields in a table.

What does the CHECK constraint do?

CHECK ensures that column values meet a specified logical condition, such as age >= 18 or salary > 0.

SQL Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT | FullStackPrep.Dev

💡 Concept Name

SQL Constraints are rules applied to columns or tables to enforce data accuracy and integrity. Common constraints include NOT NULL, UNIQUE, CHECK, and DEFAULT.

📘 Quick Intro

Constraints act as data gatekeepers. They help ensure that only valid and meaningful data is inserted into the database. Whether it’s preventing nulls, enforcing uniqueness, applying conditions, or setting default values—constraints keep the data clean and consistent.

🧠 Analogy / Short Story

Imagine a quality control system in a factory. Every product is checked before leaving: defective ones (NULLs) are rejected, duplicates are flagged (UNIQUE), certain measurements must meet standards (CHECK), and default labels are applied if missing (DEFAULT). SQL constraints play this quality control role for database records.

🔧 Technical Explanation

NOT NULL: Ensures a column cannot contain NULL values.
UNIQUE: Guarantees that all values in a column are distinct.
CHECK: Validates data based on logical expressions (e.g., Age > 0).
DEFAULT: Assigns a value automatically if none is provided.
Constraints can be applied at column or table level during CREATE or ALTER TABLE.

🎯 Purpose & Use Case

✅ Avoid NULLs in required fields using NOT NULL.
✅ Enforce business rules like minimum salary or positive age with CHECK.
✅ Prevent duplicate emails or usernames using UNIQUE.
✅ Pre-fill creation dates or statuses using DEFAULT.

💻 Real Code Example

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 18),
    CreatedAt DATETIME DEFAULT GETDATE()
);

❓ Interview Q&A

Q1: What is the purpose of SQL constraints?
A: They enforce rules on data in a table to ensure accuracy and integrity.

Q2: What does the NOT NULL constraint do?
A: It prevents the column from having null (empty) values.

Q3: What is a UNIQUE constraint?
A: It ensures all values in a column or combination of columns are unique.

Q4: When would you use a CHECK constraint?
A: When you want to enforce specific logical conditions, like salary > 0 or age >= 18.

Q5: What happens if a DEFAULT constraint is set and no value is provided?
A: The default value is automatically inserted into the column.

Q6: Can multiple constraints be applied to the same column?
A: Yes, for example, a column can be both NOT NULL and UNIQUE.

Q7: What is the difference between PRIMARY KEY and UNIQUE?
A: PRIMARY KEY also implies NOT NULL, while UNIQUE allows NULL values unless restricted.

Q8: Can you alter a table to add a constraint later?
A: Yes, using ALTER TABLE with ADD CONSTRAINT.

Q9: What happens if a CHECK constraint fails during insert?
A: The insert or update operation is aborted with an error.

Q10: Is DEFAULT a server-side or client-side constraint?
A: It's server-side—the server inserts the value automatically if none is provided.

📝 MCQs

Q1. Which constraint prevents NULL values?

UNIQUE
DEFAULT
NOT NULL
CHECK

Q2. Which constraint ensures values are unique?

NOT NULL
CHECK
UNIQUE
DEFAULT

Q3. What does CHECK do?

Assigns default value
Prevents NULL
Validates data with a logical rule
Indexes the column

Q4. What is the result of DEFAULT constraint?

Removes duplicates
Validates input
Sets value if none provided
Joins tables

Q5. Which constraint enforces Age >= 18?

DEFAULT
NOT NULL
CHECK
UNIQUE

Q6. Can UNIQUE allow NULLs?

Yes
No
Only one NULL
Only in SQL Server

Q7. Which constraint is automatically NOT NULL?

CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY

Q8. Can you add a constraint after table creation?

No
Yes
Only for CHECK
Only for NOT NULL

Q9. What function is often used with DEFAULT?

LEN()
NOW()
GETDATE()
SUM()

Q10. Which SQL keyword adds a constraint?

INSERT RULE
DEFINE RULE
ADD CONSTRAINT
SET CONSTRAINT

💡 Bonus Insight

Using constraints helps enforce business logic at the database layer—reducing dependency on client-side validations. Constraints like CHECK and DEFAULT can reduce bugs by handling rules consistently, especially in systems with multiple data entry points or APIs.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon

SQL Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT