CODEWITHSUNDEEP
sqlpostgresqlcheck-constraints
user12818580
user12818580

Reputation:

Constraint on postgresql

Hi I have a Receipt table (Id, total, Issue Date, Status, Operator) and an Employee table (ID, Operator ID, ...).

I have to define the following constraint:

An operator cannot have more than one active receipt (status) at the same time per day.

This is my constraint:

ALTER TABLE Receipt
ADD CONSTRAINT uniquenessScontrinoAttivo
CHECK (NOT EXISTS (SELECT I.ID, S. Date, COUNT (S.Id) AS Count
FROM Receipt as S NATURAL JOIN Employee
WHERE S.Status = 'Active'
GROUP BY I.ID, S.Date
HAVING Count> 1));

The error given to me concerns the fact that a subquery cannot exist. This constraint I tried to insert it in the Receipt table by clicking on constraint and specifying in check all that written above.

Upvotes: 1

Views: 88

Answers (1)

GMB
GMB

Reputation: 222432

If I followed you correctly, you can use a partial unique index for this:

create unique index unique_active_receipt
on receipt (operator, date)
where status = 'active';

Upvotes: 2

Related Questions