CODEWITHSUNDEEP
sqlpostgresqlcheck-constraints
Tom
Tom

Reputation: 4642

SQL check constraint with multiple conditions

I have a table called timesheets. In that table there are 4 columns for which I want to create a constraint so that only the following combinations are possible:

This is the table:

CREATE TABLE IF NOT EXISTS timesheets
(
    id               uuid        NOT NULL DEFAULT gen_random_uuid(),
    created_at       timestamptz NOT NULL DEFAULT current_timestamp,
    updated_at       timestamptz NOT NULL DEFAULT current_timestamp,
    deleted_at       timestamptz NULL,

    -- Where and who (check if location_id or customer_id is set then require user)
    location_id      uuid        NULL,
    customer_id      uuid        NULL,
    user_id          uuid        NULL,

    -- Or what... BUT not both
    task_schedule_id uuid        NULL,

    -- Billing
    billable         bool        NOT NULL DEFAULT TRUE,
    billed_at        timestamptz NULL,
    
    -- fks and pk
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (task_schedule_id) REFERENCES task_schedules (id),
    FOREIGN KEY (location_id) REFERENCES locations (id),
    FOREIGN KEY (customer_id) REFERENCES customers (id),
    PRIMARY KEY (id)
);

And this is what I have so far:

ALTER TABLE timesheets
    ADD constraint only_one_group
        check (
                ((user_id is null and customer_id is null and location_id is null) and
                 task_schedule_id is not null)
                or
                (user_id is not null and not (customer_id is null and location_id is null) and
                 (customer_id is null or location_id is null) and
                 task_schedule_id is null)
            );

The context is that a task_schedule links to a task which can contain a location_id and or a customer_id. The idea is that timesheets can be created globally or in combination with tasks.

Upvotes: 0

Views: 2180

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You can write the constraints as:

ALTER TABLE timesheets
  ADD constraint just_user__or__location_or_customer_with_user__or__just_task check (
      (
        user_id is not null
        and task_schedule_id is null
        and (
          (location_id is null and customer_id is null)
          and (location_id is not null or customer_id is not null)
        )
      ) or (
        (location_id is not null or customer_id is not null)
        and not (location_id is not null and customer_id is not null)
        and user_id is not null
      ) or (
        task_schedule_id is not null
        and user_id is null
        and location_id is null
        and customer_id is null
      )
    );

Upvotes: 3

Related Questions