Database constraints are rules enforced on data columns in a table. They ensure the accuracy, reliability, and integrity of the data by limiting the type of data that can be entered. If you try to perform an action that violates a constraint, the action is aborted.

Let’s use a simple Employees table to understand each one.

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL (empty) value. This means you must provide a value for that column when a new record is inserted or an existing record is updated.

Purpose: To enforce that a field must always contain a value. You cannot create a record without it.
Example: In an Employees table, you would want every employee to have a name.SQLCREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255) NOT NULL, Department VARCHAR(100) ); With this rule, you cannot add a new employee without specifying their first and last names.

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or a set of columns) are different from one another.

Purpose: To prevent duplicate values in a column that is not the primary key.
Key Difference from Primary Key: Unlike a primary key, a unique constraint allows one NULL value.
Example: In an Employees table, each employee might have an optional, unique company email address.
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(255), Email VARCHAR(255) UNIQUE );
This ensures that no two employees can have the same email address, but an employee can be added without an email (it would be NULL).

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition or a set of conditions.

Purpose: To validate the data and ensure it meets certain business rules before being saved.
Example: You might want to ensure that the Age of an employee is over 18 or that the Salary is greater than a certain amount.
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(255), Age INT CHECK (Age >= 18), Salary DECIMAL(10, 2) CHECK (Salary > 25000.00) );
If you try to insert an employee with Age = 17, the database will reject the transaction.

DEFAULT Constraint

The DEFAULT constraint is used to provide a default value for a column when no value is specified during an insert operation.

Purpose: To automatically insert a predefined value if a user doesn’t provide one.
Example: If a new employee is added without specifying their department, you might want it to automatically be set to ‘Unassigned’.
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(255), Department VARCHAR(100) DEFAULT 'Unassigned' );
Now, if you add an employee and only provide their ID and name, their Department will automatically be ‘Unassigned’.

Summary Table

Constraint	Purpose	Example
NOT NULL	Ensures a column cannot be empty.	An employee’s name must be provided.
UNIQUE	Ensures all values in a column are different.	No two employees can have the same email.
CHECK	Ensures values meet a specific condition.	Employee’s age must be 18 or older.
DEFAULT	Provides a default value if none is given.	If no department is specified, it becomes ‘Unassigned’.

MCQ

Q1: What does the NOT NULL constraint ensure in a column?
A. The column must have a unique value.
B. The column cannot have a NULL value.
C. The column must always have a default value.
D. The column must be a primary key.

Answer: B

Q2: Which of the following is a valid SQL command to enforce NOT NULL on a column?
A. ALTER TABLE Students ADD Age INT UNIQUE;
B. ALTER TABLE Students MODIFY Age INT DEFAULT NULL;
C. ALTER TABLE Students MODIFY Age INT NOT NULL;
D. ALTER TABLE Students DROP COLUMN Age NOT NULL;

Answer: C

Q3: What is the primary function of the UNIQUE constraint?
A. Ensures all values in the column are NOT NULL.
B. Ensures all values in the column are distinct.
C. Allows duplicate values in a column.
D. Restricts the column to numeric data only.

Answer: B

Q4: Can a column with a UNIQUE constraint accept NULL values?
A. Yes, but only one NULL value.
B. No, it cannot accept any NULL values.
C. Yes, with no restrictions on the number of NULL values.
D. It depends on the database version.

Answer: A

Q5: What does the CHECK constraint do?
A. Ensures no NULL values are inserted into the column.
B. Ensures the column value satisfies a specified condition.
C. Ensures the column value is unique.
D. Ensures the column has a default value.

Answer: B

Q6: Which of the following is an example of a valid CHECK constraint?
A. CHECK (Salary > 0)
B. CHECK UNIQUE (ID)
C. CHECK NOT NULL (Age)
D. CHECK DEFAULT (Status = 'Active')

Answer: A

Q7: What will happen if you insert a row that violates a CHECK constraint?
A. The database accepts the row but generates a warning.
B. The database rejects the row.
C. The database automatically adjusts the value to meet the condition.
D. The CHECK constraint is ignored.

Answer: B

Q8: What is the purpose of the DEFAULT constraint?
A. To provide a default value if no value is provided for the column.
B. To ensure the column values are unique.
C. To validate the data against a specific condition.
D. To prevent the column from having a NULL value.

Answer: A

Q9: Which of the following SQL statements correctly uses the DEFAULT constraint?
A. CREATE TABLE Orders (Status VARCHAR(20) DEFAULT 'Pending');
B. CREATE TABLE Orders (Status DEFAULT 'Pending' NOT NULL);
C. ALTER TABLE Orders ADD COLUMN Status VARCHAR(20) UNIQUE;
D. ALTER TABLE Orders MODIFY COLUMN Status DEFAULT CHECK('Pending');

Answer: A

Q10: If a DEFAULT constraint is defined for a column, what happens when a new row is inserted without a value for that column?
A. The row is rejected.
B. The default value is assigned to the column.
C. The column is left empty.
D. A runtime error occurs.

Answer: B

Q11: Which combination of constraints would be most appropriate for a Primary Key column?
A. NOT NULL and DEFAULT
B. NOT NULL and UNIQUE
C. UNIQUE and CHECK
D. DEFAULT and CHECK

Answer: B

Q12: Which of the following constraints can be applied at both column and table levels?
A. NOT NULL and DEFAULT
B. CHECK and UNIQUE
C. NOT NULL and CHECK
D. DEFAULT and UNIQUE

Answer: B

Quick Tips for Exam

NOT NULL: Mandatory data in a column.
UNIQUE: No duplicate values (one NULL allowed).
CHECK: Conditional validation for data integrity.
DEFAULT: Fills columns with pre-defined values if not explicitly provided.

Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT

NOT NULL Constraint

UNIQUE Constraint

CHECK Constraint

DEFAULT Constraint

Summary Table

MCQ

Quick Tips for Exam