Database Constraints in SQL
Ensuring Data Integrity in Relational
Databases
What Are Domain Constraints?
• Define valid values an attribute (column) can
hold.
• Specify data types (e.g., integer, string, date).
• Add conditions like value range or allowed
patterns.
Check Constraint
• Enforces specific conditions on column values.
• Defined during table creation or alteration.
Check Constraint - SQL Syntax
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Salary DECIMAL(10, 2) CHECK (Salary > 0),
Department VARCHAR(50) CHECK (Department IN ('HR', 'IT', 'Finance'))
);
NOT NULL Constraint
• Ensures a column cannot be NULL.
• Used to enforce presence of data.
NOT NULL Constraint - SQL
Example
CREATE TABLE Employees (
EmployeeID INT,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50)
);
Key Constraints Overview
• Ensure uniqueness and data integrity.
• Identify rows and establish relationships.
• Types: Primary Key, Unique, Foreign Key.
Primary Key Constraint
• Uniquely identifies rows.
• Cannot be NULL. One per table.
Primary Key - SQL Example
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50)
);
Primary Key - Insert Examples
-- Valid
INSERT INTO Students VALUES (1, 'John');
-- Invalid (duplicate)
INSERT INTO Students VALUES (1, 'Jane');
Unique Constraint
• Ensures column values are unique.
• Allows NULL values.
Unique Constraint - SQL Example
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductCode VARCHAR(20) UNIQUE
);
Unique Constraint - Insert
Examples
-- Valid
INSERT INTO Products VALUES (1, 'P001');
-- Invalid (duplicate code)
INSERT INTO Products VALUES (2, 'P001');
Foreign Key Constraint
• Links two tables.
• Enforces referential integrity.
Foreign Key - SQL Example
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Entity Integrity Constraint
• Primary key attributes cannot be NULL.
• Ensures record uniqueness.
Entity Integrity - SQL Example
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL
);
Referential Integrity Constraint
• Maintains consistency among related tables.
• Foreign key values must exist in referenced
table.
Referential Integrity - SQL Example
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Tuple Uniqueness Constraint
• Ensures unique combination of column values.
• Defined using UNIQUE(column1, column2).
Tuple Uniqueness - SQL Example
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductCode VARCHAR(20) NOT NULL,
ProductName VARCHAR(100),
Category VARCHAR(50),
Price DECIMAL(10, 2),
UNIQUE (ProductCode, Category)
);
Summary
• Domain Constraints control allowable values.
• Key Constraints maintain uniqueness and
integrity.
• Integrity Constraints preserve relationships
and accuracy.
Thank You
• Questions?
• [Your Contact Info or Institution]
