Constraints in DBMS

Importance of Constraints in DBMS

Constraints in a Database Management System (DBMS) are rules applied to table columns to maintain the integrity, accuracy, and consistency of the data.

🔑 Why Are Constraints Important?

Data Integrity: Ensures only valid data is stored.
Consistency: Keeps uniform data values.
Accuracy: Matches real-world scenarios.
Reliability: Makes the database trustworthy.
Business Logic: Enforces rules directly at the database level.

📚 Types of Constraints with Examples

1. NOT NULL Constraint

Ensures a column cannot have a NULL value.

CREATE TABLE Employee (
  ID INT NOT NULL,
  Name VARCHAR(100) NOT NULL,
  Email VARCHAR(100)
);

2. UNIQUE Constraint

Ensures all values in a column are different.

CREATE TABLE Users (
  UserID INT PRIMARY KEY,
  Email VARCHAR(100) UNIQUE
);

3. PRIMARY KEY Constraint

Combines NOT NULL and UNIQUE. Uniquely identifies each record.

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);

4. FOREIGN KEY Constraint

Links one table to another. Ensures referential integrity.

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

5. CHECK Constraint

Limits the value range that can be placed in a column.

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Salary DECIMAL(10, 2),
  CHECK (Salary >= 0)
);

6. DEFAULT Constraint

Assigns a default value if no value is provided.

CREATE TABLE Members (
  MemberID INT PRIMARY KEY,
  JoinDate DATE DEFAULT CURRENT_DATE
);

✅ Summary Table

Constraint	Purpose	Ensures	Example Use Case
NOT NULL	Prevents null entries	Required fields	Name, Email
UNIQUE	Ensures all values are distinct	No duplicate entries	Email, National ID
PRIMARY KEY	Unambiguously identifies each row	Entity identity	StudentID
FOREIGN KEY	Maintains referential integrity	Valid relationships	CustomerID in Orders
CHECK	Enforces domain of values	Value constraints	Age >= 18, Salary >= 0
DEFAULT	Sets default values	Auto-fill if none provided	Join date as CURRENT_DATE

Trending Flash News