100 Days of SQL

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:

1. NOT NULL constraint: This constraint ensures that a column cannot have a NULL value.
2. PRIMARY KEY constraint: This constraint ensures that a column or a combination of columns uniquely identifies each row in the table.
3. UNIQUE constraint: This constraint ensures that each value in a column or a combination of columns is unique across all rows in the table.
4. 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.
5. 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.