SQL Constraints

SQL Constraints are rules applied to table columns that control the type of data that can be stored in a database. Constraints help maintain data accuracy, consistency, reliability, and integrity by preventing invalid data from entering database tables.

In real-world database systems, thousands or even millions of records are stored and processed daily. Without constraints, users could enter duplicate values, invalid information, or inconsistent data. SQL constraints act as safeguards that ensure only valid data is stored.

For example, a student ID should be unique for every student, and an employee's salary should never be negative. Constraints make these rules enforceable automatically by the database management system.

---

Why SQL Constraints are Important?

Constraints play a crucial role in database design because they ensure data remains accurate and meaningful throughout the lifecycle of an application.

• Data Integrity: Ensures only valid data is stored.
• Prevents Duplicate Records: Avoids repeated entries.
• Improves Data Consistency: Maintains uniform values.
• Reduces Errors: Prevents invalid user input.
• Supports Relationships: Connects related tables correctly.
• Enhances Database Reliability: Makes applications more trustworthy.

---

Types of SQL Constraints

SQL provides several types of constraints to enforce different rules on table columns.

Constraint	Purpose
NOT NULL	Prevents NULL values in a column
UNIQUE	Ensures all values are unique
PRIMARY KEY	Uniquely identifies each record
FOREIGN KEY	Creates relationships between tables
CHECK	Validates values using conditions
DEFAULT	Assigns a default value automatically

---

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values. Every record must provide a value for that column.

Syntax

ColumnName DataType NOT NULL

Example

CREATE TABLE Student (
StudentID INT,
StudentName VARCHAR(100) NOT NULL
);

In this example, StudentName cannot be left empty when inserting records.

Advantages of NOT NULL

• Ensures mandatory data is provided.
• Prevents incomplete records.
• Improves data quality.

---

2. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. Duplicate values are not allowed.

Syntax

ColumnName DataType UNIQUE

Example

CREATE TABLE Student (
StudentID INT,
Email VARCHAR(100) UNIQUE
);

Each email address must be unique in the table.

Practical Uses

• Email addresses
• Employee IDs
• Mobile numbers
• Username fields

---

3. PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies every record in a table. It combines the properties of NOT NULL and UNIQUE.

Syntax

ColumnName DataType PRIMARY KEY

Example

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100)
);

No two students can have the same StudentID, and StudentID cannot be NULL.

Characteristics of Primary Key

• Must be unique.
• Cannot contain NULL values.
• Only one primary key per table.
• Can consist of multiple columns (Composite Key).

---

4. FOREIGN KEY Constraint

A FOREIGN KEY creates a relationship between two tables. It ensures that values in one table match values in another table.

Example

CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT,
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);

Employees can only be assigned to departments that already exist in the Department table.

---

5. CHECK Constraint

The CHECK constraint restricts values based on a specified condition.

Example

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Age INT CHECK (Age >= 18)
);

Only students with age 18 or greater can be inserted.

Common Uses

• Age validation
• Salary limits
• Product quantity checks
• Exam score validation

---

6. DEFAULT Constraint

The DEFAULT constraint automatically assigns a value when no value is provided.

Example

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
Status VARCHAR(20) DEFAULT 'Active'
);

If no status is specified, the value "Active" is automatically assigned.

---

Real-World Example

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Age INT CHECK (Age >= 18),
Course VARCHAR(50) DEFAULT 'B.Tech'
);

This table demonstrates multiple constraints working together to maintain data quality and integrity.

---

Difference Between PRIMARY KEY and UNIQUE

Feature	PRIMARY KEY	UNIQUE
Duplicate Values	Not Allowed	Not Allowed
NULL Values	Not Allowed	Usually One NULL Allowed
Count Per Table	One	Multiple

---

Best Practices for Using Constraints

• Always define a PRIMARY KEY.
• Use FOREIGN KEY relationships whenever possible.
• Apply NOT NULL to mandatory fields.
• Use CHECK constraints for validation.
• Use UNIQUE for emails and usernames.
• Use DEFAULT values for common entries.
• Keep constraints simple and meaningful.

---

Common Mistakes to Avoid

• Not defining primary keys.
• Using too many unnecessary constraints.
• Ignoring foreign key relationships.
• Allowing NULL values in important columns.
• Using incorrect CHECK conditions.

---

Advantages of SQL Constraints

• Improves data accuracy.
• Maintains database integrity.
• Reduces application-level validation.
• Prevents invalid entries.
• Creates reliable relationships between tables.
• Enhances overall database performance and consistency.

---

Conclusion

SQL Constraints are essential tools for maintaining data integrity and ensuring the accuracy of information stored in databases. They help enforce business rules, prevent invalid data, and improve overall database reliability.

By understanding and correctly implementing NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT constraints, developers can build secure, efficient, and well-structured database systems.