Day 34 – SQL Constraints
In SQL, constraints are used to specify rules and restrictions on the data that can be stored in a table. They ensure data integrity and consistency by preventing invalid data from being entered into the table.
There are several types of constraints that can be defined on a table, including:
- NOT NULL constraint: This constraint ensures that a column cannot have a NULL value.
- PRIMARY KEY constraint: This constraint ensures that a column or a combination of columns uniquely identifies each row in the table.
- UNIQUE constraint: This constraint ensures that each value in a column or a combination of columns is unique across all rows in the table.
- FOREIGN KEY constraint: This constraint ensures that the values in a column or a combination of columns in one table correspond to the values in a primary key column or a unique key column in another table.
- CHECK constraint: This constraint ensures that the values in a column satisfy a specified condition.
Here’s an example of how to define constraints on a table:
CREATE TABLE employees ( id INT NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL, salary DECIMAL(10, 2) NOT NULL, department_id INT NOT NULL, CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id), CONSTRAINT ck_salary CHECK (salary > 0) );
In this example, we define a primary key constraint on the “id” column and a foreign key constraint on the “department_id” column that references the “id” column in the “departments” table. We also define a check constraint on the “salary” column to ensure that it is greater than 0.
Constraints can also be added or removed using the ALTER TABLE statement. For example, to add a unique constraint on the “name” column of the “employees” table, you can use the following statement:
ALTER TABLE employees ADD CONSTRAINT uk_name UNIQUE (name);
And to remove the unique constraint on the “name” column, you can use the following statement:
ALTER TABLE employees DROP CONSTRAINT uk_name;
Constraints play an important role in maintaining data integrity and ensuring the accuracy and reliability of the data stored in a table.