SQL Tutorial

✨ SQL CHECK Constraint

The CHECK constraint in SQL is used to limit the values that can be inserted into a column. It ensures that data entered into the database meets specific conditions or requirements. This constraint can be applied on a column or even on multiple columns.

1️⃣ Creating a Table with a CHECK Constraint

You can add a CHECK constraint while creating a table to ensure that data entered into specific columns follows certain rules.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  salary DECIMAL(10, 2),
  CHECK (age >= 18)
);

In this example, the CHECK constraint ensures that only employees aged 18 or older can be inserted into the employees table.

2️⃣ Adding a CHECK Constraint to an Existing Table

You can also add a CHECK constraint to an existing table using the ALTER TABLE statement.

ALTER TABLE employees
ADD CHECK (salary > 0);

This statement adds a check to ensure that the salary for every employee is positive. If someone tries to insert a negative salary, the database will reject the insert.

3️⃣ Using Multiple Conditions in a CHECK Constraint

A CHECK constraint can have multiple conditions, and all conditions must be satisfied for the insertion or update to be allowed.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  salary DECIMAL(10, 2),
  CHECK (age >= 18 AND salary > 0)
);

Here, both conditions must be true for an employee to be successfully added to the employees table: the age must be 18 or older, and the salary must be positive.

4️⃣ Removing a CHECK Constraint

If you no longer need a CHECK constraint, you can remove it with the ALTER TABLE statement.

ALTER TABLE employees
DROP CONSTRAINT CHECK;

This will remove the CHECK constraint, allowing the insertion of data that was previously restricted by the check.

🔧 Key Considerations

• Data Integrity: The CHECK constraint ensures that only valid data can be inserted, preventing errors and inconsistencies.
• Multiple Conditions: You can combine multiple conditions using AND or OR to create more complex rules.
• Limitations: While useful, the CHECK constraint does not support subqueries or referencing other tables.