CODEWITHSUNDEEP
sqlpostgresql
pedrodalnk
pedrodalnk

Reputation: 51

How to set constraints based on the values of columns?

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?

Upvotes: 0

Views: 45

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 2

Related Questions