Constraint on postgresql

Question

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.

GMB · Accepted Answer · 2020-03-10 14:31:46Z

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';

answered Mar 10, 2020 at 14:31

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

user12818580 Over a year ago

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?

GMB Over a year ago

@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.

user12818580 Over a year ago

if I didn't want to use an index?

GMB Over a year ago

@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.

Collectives™ on Stack Overflow

Constraint on postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related