Constraints are the rules and regulations which are applied to the table column which enforces yours to store valid data and prevents users to store irrelevant data. There are two levels :
- column level constraint
- table level constraint
In SQL, a constraint is a rule or condition that is enforced on data in a table. Constraints are used to ensure the accuracy and integrity of the data stored in a database. They define certain properties that data in a table must adhere to, preventing the entry of invalid or inconsistent information. There are various levels of constraints in SQL, including:
- Column-level Constraints:
- NOT NULL: Ensures that a column cannot have NULL values.
- UNIQUE: Ensures that all values in a column are unique.
- CHECK: Enforces a condition that all values in a column must satisfy.
Example:
CREATE TABLE example_table (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT CHECK (age >= 18)
);
- Table-level Constraints:
- PRIMARY KEY: Identifies a unique record in a table.
- FOREIGN KEY: Establishes a link between data in two tables.
- CHECK (at table level): Enforces a condition for the entire table.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
- Database-level Constraints:
- UNIQUE (across tables): Enforces uniqueness across multiple tables.
- CHECK (across tables): Enforces a condition across multiple tables.
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
department_id INT,
CONSTRAINT chk_department CHECK (department_id IN (1, 2, 3))
);
Constraints play a crucial role in maintaining data integrity and ensuring that the data stored in a database follows predefined rules. They help in preventing errors, inconsistencies, and inaccuracies in the data, contributing to the overall reliability of the database.