MySQL trying to ADD a multiple CHECK constraints

Question

I'm trying to make a CHECK constraint that checks that employees who work in department 1 must earn >=24000.

this is what i've used:

ALTER TABLE employees
ADD CONSTRAINT min_sal CHECK(dept_no = 1 AND salary >=24000);

However, I'm getting 'check constraint min_sal is violated' error message.

How do i add a constraint that checks if the employee is working in department 1, then they must earn >=24000?

Answer 1

checks that employees who work in department 1 must earn >=24000.

I.e. check that dept_no is not department 1, or salary >= 24000 (in case of department 1.)

ALTER TABLE employees
ADD CONSTRAINT min_sal CHECK(dept_no <> 1 OR salary >=24000);

Edit: To find already existing rows violating the constraint:

select * from employees
where dept_no = 1 and salary < 24000