0

The task is to write an alter query for general constraints. Specifically: "In Employee, if EmployeeLevel is NULL, then EmployeeSalary must also be NULL". If anyone could explain a correct solution, it would be much appreciated!

ALTER TABLE Employee
ADD CONSTRAINT 
CHECK (
EmployeeLevel IS NULL
AND EmployeeSalary IS NULL
);
John Doe
  • 1
  • Is the inverse also true? If EmployeeLevel is not null must EmployeeSalary also not be null? What about ranges? Is a negative salary allowed or an empty EmployeeLevel (if it is a varchar). – Igor May 12 '20 at 18:16
  • I believe that if employeeLevel is not null, then employeeSalary can be null. And vise-versa. – John Doe May 12 '20 at 18:23

2 Answers2

1

The statement "If employeeLevel is not null, then employeeSalary can be null" can be accepted. The "and vise-versa" cannot be. That would say "If employeeSalary is not null, then employeeLevel can be null" but that contradicts the original problem statement "if EmployeeLevel is NULL, then EmployeeSalary must also be NULL". The result then is a straight forward OR condition and IMHO the clearest expression of the intent: 

check ((EmployeeLevel is null and EmployeeSalary is null) or (EmployeeLevel is not null))
Belayer
  • 13,578
  • 2
  • 11
  • 22
0

Here is one method:

CHECK (EMPLOYELEVEL IS NOT NULL OR EmployeeSalary IS NULL)

You might find the NOT logic to be clearer:

CHECK (NOT (EMPLOYELEVEL IS NULL AND EmployeeSalary IS NOT NULL))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786