How to set constraints based on the values of columns?

Question

I'm working on a table of requests to link an user to another user and these requests must be approved to happen.

CREATE TABLE IF NOT EXISTS requests (
    requested_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    approved_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    denied_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    user1_id int NOT NULL,
    user2_id int NOT NULL,
    UNIQUE(user1_id, user2_id),

    CONSTRAINT fk_requests_user FOREIGN KEY (user1_id) REFERENCES user(id),
    CONSTRAINT fk_requests_user FOREIGN KEY (user2_id) REFERENCES user(id), );

Right now I have a constraint that prevent further requests of one user to another through:

UNIQUE(user1_id, user2_id)

But I want to be able to use this as history, so I need a way to these foreign keys be UNIQUE if both approved_at and denied_at are '1970-01-01 00:00:00'(zero value of TIMESTAMP).

I thought of this logic, though SYNTACTICALLY WRONG:

UNIQUE(user1_id, user2_id, approved_at='1970-01-01 00:00:00', denied_at='1970-01-01 00:00:00')

How can I make it possible?

user330315 · Accepted Answer · 2022-03-09 14:57:57Z

2

Use a partial unique index:

create unique index on requests (requester_id, requested_agency_id)
where approved_at='1970-01-01 00:00:00'
  and denied_at='1970-01-01 00:00:00'

Having magic values like that is usually not a good choice. Use null to indicate the absence of a value. Or -infinity if you need range queries to work without checking for null.

answered Mar 9, 2022 at 14:57

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to set constraints based on the values of columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related