Data Integrity and Constraints in SQL
Data integrity ensures the accuracy, consistency, and reliability of data in a database. Constraints are rules applied at the database level to enforce data integrity, ensuring that the data adheres to specific standards and logical relationships. This chapter provides a detailed explanation of Data Integrity and Constraints, progressing from basic concepts to advanced topics.
Basics of Data Integrity
What is Data Integrity?
Data integrity is the practice of maintaining and ensuring the accuracy, consistency, and reliability of data throughout its lifecycle. It ensures that:
- Data remains accurate and trustworthy.
- There are no unintended changes or data corruption.
Types of Data Integrity
- Entity Integrity: Ensures each row (record) in a table has a unique identifier (Primary Key).
- Referential Integrity: Maintains consistency between related tables using Foreign Keys.
- Domain Integrity: Ensures data adheres to predefined formats and values.
- User-Defined Integrity: Implements custom rules using triggers, procedures, or constraints.
SQL Constraints Overview
Constraints are rules applied to table columns to restrict the type of data that can be stored. These include:
- NOT NULL: Prevents null values.
- UNIQUE: Ensures all values in a column are unique.
- PRIMARY KEY: Uniquely identifies a record in the table.
- FOREIGN KEY: Maintains referential integrity between tables.
- CHECK: Ensures a condition is met.
- DEFAULT: Sets a default value for a column if no value is provided.
Implementing Constraints
NOT NULL Constraint
Ensures that a column cannot contain NULL values.
Example:
CREATE TABLE employees (
id INT NOT NULL,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2) NOT NULL
);
Here, id, name, and salary cannot have null values.
UNIQUE Constraint
Ensures all values in a column are unique.
Example:
CREATE TABLE customers (
customer_id INT UNIQUE,
email VARCHAR(255) UNIQUE
);
- No two customers can have the same
customer_idoremail.
PRIMARY KEY Constraint
Uniquely identifies each record in a table. Combines the properties of NOT NULL and UNIQUE.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
FOREIGN KEY Constraint
Ensures referential integrity by linking one table to another.
Example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
The customer_id in orders must match an existing customer_id in customers.
CHECK Constraint
Enforces a condition on data values.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
- The
pricemust always be greater than 0, andquantitycannot be negative.
DEFAULT Constraint
Specifies a default value if none is provided.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
If created_at is not specified, it defaults to the current timestamp.
Advanced Topics in Data Integrity
Composite Primary Keys
A primary key that consists of multiple columns.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
Combines student_id and course_id to uniquely identify each record.
Cascading Actions with Foreign Keys
Cascading actions ensure data consistency when rows in related tables are updated or deleted.
Example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
ON DELETE CASCADE: Deletes orders when a customer is deleted.ON UPDATE CASCADE: Updates related rows if thecustomer_idchanges.
Enforcing Complex Rules with CHECK
You can enforce complex conditions using CHECK.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT CHECK (age >= 18 AND age <= 65),
salary DECIMAL(10, 2) CHECK (salary >= 30000)
);
- Ensures
ageis between 18 and 65 andsalaryis at least 30,000.
Deferred Constraints
Some databases (like PostgreSQL) allow deferring constraint checks until a transaction commits.
Example (PostgreSQL):
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
Managing Constraints
Adding Constraints to Existing Tables
You can add constraints to existing tables using ALTER TABLE.
Example:
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);
Dropping Constraints
Remove constraints if needed.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
Viewing Constraints
MySQL
SHOW CREATE TABLE employees;
PostgreSQL
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
Real-World Applications of Constraints
- E-Commerce: Ensuring product prices are non-negative.
- Banking: Maintaining referential integrity for transactions.
- Healthcare: Enforcing domain rules, such as valid ranges for patient ages.
Data integrity and constraints are vital for building reliable and robust databases. By enforcing rules at the database level, you minimize data anomalies, maintain consistency, and enhance application reliability. This chapter provided a thorough understanding of constraints, from their basics to advanced applications, with practical examples. Properly using constraints ensures that your database remains a trustworthy source of truth. Happy coding !❤️