How to add a CHECK constraint to an existing table using the ALTER TABLE statement

In Oracle, you can add a CHECK constraint to an existing table using the ALTER TABLE statement. Here's the syntax:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name CHECK (condition);

Example:

Suppose you have a table employees and you want to add a CHECK constraint to ensure that the salary column is greater than 3000.

ALTER TABLE employees 
ADD CONSTRAINT chk_salary CHECK (salary > 3000);

Adding a CHECK Constraint on Multiple Columns:

You can also apply a CHECK constraint involving multiple columns. For example:

ALTER TABLE employees 
ADD CONSTRAINT chk_salary_bonus CHECK (salary >= bonus);

Notes:

• The CHECK constraint ensures that all new or updated rows satisfy the condition.

• If existing rows violate the condition, adding the constraint will fail unless NOVALIDATE is used.

• You can drop a CHECK constraint using:

  
  ALTER TABLE employees DROP CONSTRAINT chk_salary;

In Oracle, a CHECK constraint cannot directly use the NOT IN clause because it does not support subqueries or set-based expressions. However, you can achieve similar functionality using AND and != conditions.

Example:

Suppose you have an employees table and want to ensure that the job_title column does not contain certain values like 'Intern' and 'Trainee'. You can define the CHECK constraint like this:

ALTER TABLE employees 
ADD CONSTRAINT chk_job_title 
CHECK (job_title NOT IN ('Intern', 'Trainee'));

Alternative Approach:

If you need more complex conditions (e.g., checking values dynamically from another table), consider using:

1. Triggers – For enforcing complex constraints.
2. Foreign Key with ON DELETE rules – If restricting values based on another table.