In SQL, constraints are used to enforce rules on the data in a table. They ensure that the data is accurate, consistent, and reliable. By using constraints, database administrators and developers can prevent incorrect or invalid data from being inserted or updated in a table, thus maintaining data integrity.
SQL constraints are vital for protecting the relationships between tables, ensuring that data meets certain conditions, and enforcing business rules. In this guide, we will explore the different types of SQL constraints and how to use them in your database.
SQL constraints are rules that are applied to columns or tables in a database. These rules help ensure the accuracy and integrity of the data stored in the database. When you define a constraint on a table, SQL checks that the data in the table adheres to these rules.
SQL supports several types of constraints, including:
Let's explore each type in more detail.
The PRIMARY KEY constraint is used to uniquely identify each row in a table. A primary key column cannot have NULL values, and each value must be unique. This constraint ensures that every record in the table is identifiable by a unique value.
CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
column2 datatype,
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(100)
);
In this example, employee_id is the primary key, meaning each employee_id must be unique.
The FOREIGN KEY constraint is used to link two tables together. It ensures that a value in one table corresponds to a valid value in another table. Typically, a foreign key in a child table points to the primary key in a parent table. This ensures referential integrity between the two tables.
CREATE TABLE child_table (
column1 datatype,
column2 datatype,
FOREIGN KEY (column1) REFERENCES parent_table (parent_column)
);
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
Here, the employees table has a foreign key (department_id) that references the primary key (department_id) in the departments table. This ensures that each employee belongs to a valid department.
The UNIQUE constraint ensures that all values in a column are different. It can be applied to one or more columns. Unlike the primary key, the UNIQUE constraint allows NULL values (but only one NULL per column in most databases).
CREATE TABLE table_name (
column1 datatype UNIQUE,
column2 datatype,
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
name VARCHAR(100)
);
In this example, the email column is unique, meaning no two employees can have the same email address.
The NOT NULL constraint ensures that a column cannot have NULL values. This is useful when a column must always contain a value, and missing data is unacceptable.
CREATE TABLE table_name (
column1 datatype NOT NULL,
column2 datatype,
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(100)
);
In this example, the name column must always contain a value. If you attempt to insert a row without a name, an error will be raised.
The CHECK constraint is used to limit the range of values that can be entered into a column. It is used to enforce specific conditions or rules on the data, such as ensuring that a column value falls within a particular range.
CREATE TABLE table_name (
column1 datatype CHECK (condition),
column2 datatype,
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2),
CHECK (salary > 0)
);
In this example, the CHECK constraint ensures that the salary column only accepts positive values greater than 0.
The DEFAULT constraint is used to provide a default value for a column when no value is specified during an insert operation. This can be useful for columns where a specific value is commonly used.
CREATE TABLE table_name (
column1 datatype DEFAULT default_value,
column2 datatype,
...
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(100) DEFAULT 'Staff'
);
In this example, if no position is specified during the insertion of a new employee, the default value 'Staff' will be used.
You can also define composite constraints, where multiple columns together form a unique constraint. This is useful when no single column can uniquely identify a row but a combination of columns can.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
CONSTRAINT constraint_name UNIQUE (column1, column2)
);
CREATE TABLE orders (
order_id INT,
product_id INT,
PRIMARY KEY (order_id, product_id)
);
In this example, the combination of order_id and product_id is the primary key, ensuring that each combination of order_id and product_id is unique.
You can combine multiple constraints in a single column or table. For example, a column can have both a NOT NULL and CHECK constraint.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2) CHECK (salary > 0),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
In this table, the name column must be non-null, the salary column must be greater than zero, and the department_id must refer to an existing department.
PRIMARY KEY and FOREIGN KEY for relationships, UNIQUE to ensure uniqueness, and CHECK for validation of data.NOT NULL constraints carefully: Avoid making columns NOT NULL if there is a chance that the data might be missing or optional in the future.