CODEWITHSUNDEEP
sqlpostgresqlcheck-constraintsgeneric-constraints
John Doe
John Doe

Reputation: 1

How Can I Write This Constraint?

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
);

Upvotes: 0

Views: 33

Answers (2)

Belayer
Belayer

Reputation: 14861

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))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269843

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))

Upvotes: 0

Related Questions