Define an SQL constraint depending on more than one attribute

Question

I have this defined type and table:

CREATE TYPE emp_role AS ENUM ('Manager','Developer','Accountant','Secretary');
CREATE TABLE employees (
employee_id int NOT NULL UNIQUE,
lastname text NOT NULL,
firstname text NOT NULL,
address text NOT NULL,
hire_date date NOT NULL,
salary numeric NOT NULL CHECK(salary > 1.500), 
emp_role emp_role,
department_id int NOT NULL
);

I want to create a constraint on this table, without modifying its definition, that depends at the same time on the attributes hire_date, salaryand emp_role, this means for example that employees with emp_role of Manager hired after 2019-21-11 can't have salarybigger than 15.000so a query like this should return an error:

INSERT INTO employees VALUES(2,'foo','bar','foostreet','2019-12-20',18.0000,'Manager',3);

I'm not familiar with how to do this

Answer 1

You can create a multi-column check constraint as follows:

CREATE TYPE emp_role AS ENUM ('Manager','Developer','Accountant','Secretary');

CREATE TABLE employees (
    employee_id int NOT NULL UNIQUE,
    lastname text NOT NULL,
    firstname text NOT NULL,
    address text NOT NULL,
    hire_date date NOT NULL,
    salary numeric NOT NULL CHECK(salary > 1500), 
    emp_role emp_role,
    department_id int NOT NULL
    CONSTRAINT CK_employee CHECK (
        NOT(
            emp_role = 'Manager' 
            AND hire_date > DATE'2019-11-21' 
            AND salary > 15000
        )
    )
);

Demo on DB Fiddle

INSERT INTO employees VALUES(2,'foo','bar','foostreet','2019-12-20',180000,'Manager',3);

ERROR:  new row for relation "employees" violates check constraint "ck_employee"
DETAIL:  Failing row contains (2, foo, bar, foostreet, 2019-12-20, 180000, Manager, 3).

INSERT INTO employees VALUES(2,'foo','bar','foostreet','2019-12-20',1,'Developer',3);

ERROR:  new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL:  Failing row contains (2, foo, bar, foostreet, 2019-12-20, 1, Developer, 3).