Constraints in SQL are rules applied to columns or tables that enforce data integrity and maintain consistency in a database. They define limitations and conditions for the values that can be stored in columns or rows.
Common Types of Constraints
Primary Key Constraint: Ensures that each row in a table has a unique identifier. It uniquely identifies each record in the table and prevents duplicate entries.
Foreign Key Constraint: Establishes a relationship between two tables by enforcing referential integrity. It ensures that values in a column (or group of columns) in one table match values in another table's primary key or unique key column(s).
Unique Constraint: Ensures that all values in a column (or group of columns) are unique and not duplicated within the table.
Check Constraint: Defines a condition that each row in a table must satisfy. It restricts the range of values that can be stored in a column.
Default Constraint: Specifies a default value for a column when no value is explicitly provided during an INSERT operation.
Syntax
The syntax for adding constraints varies slightly between different SQL database systems. Here are general examples:
Primary Key Constraint:
CREATE TABLE table_name (
column_name data_type PRIMARY KEY,
...
);
Foreign Key Constraint:
CREATE TABLE table_name (
column_name data_type,
...
FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column)
);
Unique Constraint:
CREATE TABLE table_name (
column_name data_type UNIQUE,
...
);
Check Constraint:
CREATE TABLE table_name (
column_name data_type CHECK (condition),
...
);
Default Constraint:
CREATE TABLE table_name (
column_name data_type DEFAULT default_value,
...
);
Example
Consider a table employees with constraints:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id),
age INT CHECK (age >= 18),
hire_date DATE DEFAULT CURRENT_DATE
);
In this example:
employee_id is the primary key.department_id is a foreign key referencing the department_id column in the departments table.age has a check constraint to ensure it's 18 or older.hire_date has a default constraint set to the current date.
Usage
Constraints play a crucial role in maintaining data integrity and enforcing business rules in a database. They help prevent data inconsistencies, ensure referential integrity, and improve overall database reliability.
Constraints in SQL are essential tools for enforcing data integrity and maintaining consistency in a database. Whether it's ensuring uniqueness, establishing relationships between tables, or enforcing business rules, understanding how to use constraints effectively is essential for SQL database design and management.
