1

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.

Improve this question

1 Answer 1

Reset to default
2

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';
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I am a university student and it is the first time that I program in sql. I don't know exactly what your code does. Can you explain it to me?
@Antonio: sure. This constraints says: for a given (operator, date) tuple, there can be only one record where status = active. You can read more about partial indexes in the Postgres documentation.
if I didn't want to use an index?
@Antonio: in Postgres, a unique index is the way to do what you want. Otherwise, you will need to implement a database trigger, which is more complex and requires more code.