Databases Reference
In-Depth Information
*
ERROR at line 1:
ORA-02290: check constraint (HR.CK1_JOBS) violated
The HR department decides that the new technical lead position has an open-
ended upper salary, so the addition is made with the following INSERT command:
insert into jobs (job_id, job_title, min_salary)
values('IT_TECHLD', 'Technical Lead', 10000);
1 row created.
Even though no maximum salary is specified, this INSERT operation still
works. A CHECK constraint condition will allow the record to be inserted if the
CHECK condition expression evaluates to either true or unknown. In this INSERT
statement, the MAX_SALARY column is NULL, and therefore the CHECK condition
expression (max_salary > min_salary) is (NULL > 10000), which evaluates
to NULL (unknown). Therefore, the CHECK condition will not prevent this row
from being inserted. However, explicit NULL checking can be performed in a
CHECK constraint by using the IS NULL or IS NOT NULL operator.
Later in the week, Janice learns that the business rule for minimum and max-
imum salary in the JOBS table has changed; if a minimum salary is specified, then
a maximum salary must also be specified. Therefore, either both salaries are
NULL or both salaries are NOT NULL. Janice decides that a new CHECK constraint
is needed to enforce this business rule, so her first step is to drop the existing con-
straint on the table:
alter table jobs drop constraint ck1_jobs;
Table altered.
The new check constraint will compare MIN_SALARY and MAX_SALARY only if
both values are NOT NULL, otherwise both values must be NULL to pass the CHECK
constraint:
alter table jobs add constraint ck1_jobs
check ((max_salary is not null and
min_salary is not null and
max_salary > min_salary)
or
(max_salary is null and min_salary is null)
);
Table altered.
