Postgres - Trigger to prohibit same records (linked - as a set)

Question

Here is my case:

TABLE 1: Tickets Columns (id, user_id)

TABLE 2: Ticket Choices Columns (id, ticket_id, choice_code)

So a user can't have a tickets with similar choices, e.g

TICKETS:

id	user_id
1	john
2	mary

Ticket Choices:

id	ticket_id	choice_code
1	1	abc
2	1	xyz

Would be allowed, but repeating the same entries for the same user/ticket owner should be considered invalid. But if the entries belong to a ticket that belongs to a different user, they should be accepted.

I have been able to do this on an application level with some validation, by querying the db first, then checking submitted data against the results.

Now, I want this to be enforced on the database level. I have read about check constraints but from my findings, when multiple tables/joins are to be used, they don't apply. Can something like this be enforced?

EDIT: More examples

Tickets:

PK: 100, user: John
PK: 102, user: John
PK: 103, user: Mary

Ticket Choices.

PK: 200, Ticket FK: 100, Choice Code: ABC
PK: 201, Ticket FK: 100, Choice Code: JFK
PK: 202, Ticket FK: 102, Choice Code: ABC
PK: 203, Ticket FK: 103, Choice Code: ABC
PK: 204, Ticket FK: 103, Choice Code: JFK

The following entries/should not be accepted once the above entries have been commited:

Ticket:

PK: 104, user: John
PK: 105, user: Mary

Ticket Choices.

PK: 205, Ticket FK: 104, Choice Code: ABC // Because a ticket choices set for John with {ABC} already exists
PK: 206, Ticket FK: 105, Choice Code: ABC
PK: 207, Ticket FK: 105, Choice Code: JFK

The last two because User Mary has a ticket with choice set {ABC, JFK} already.

Thanks for the clarification... by this logic, shouldn't ticket choices id 202 have failed because John/ABC already exists? — Hambone
– Hambone, Commented Mar 16, 2021 at 8:41
No, because 202 belongs to a different ticket, 102. So first ticket set for ticket 100 = {ABC, JFK} and second set, ticket 102 = {ABC}, so by the rules I'm trying to establish this user, John, doesn't have duplicate sets. — Redgren Grumbholdt
– Redgren Grumbholdt, Commented Mar 16, 2021 at 10:53
I appreciate your input though! Might have to consider denormalizing the choices table as you suggested....Gubba nub nub doo rah kah. — Redgren Grumbholdt
– Redgren Grumbholdt, Commented Mar 16, 2021 at 11:00

Hambone · Accepted Answer · 2021-03-16 09:03:59Z

1

I think you want a unique constraint:

alter table ticket_choices
add constraint ticket_choices_u1 unique (ticket_id, choice_code)

These will run fine:

insert into ticket_choices values (1, 1, 'abc');
insert into ticket_choices values (2, 1, 'xyz');

This will throw an error:

insert into ticket_choices values (3, 1, 'abc');

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "ticket_choices_u1" Detail: Key (ticket_id, choice_code)=(1, abc) already exists.

You can only have one PK, but a unique constraint can be different from the PK.

-- EDIT --

I can't imagine this is particular efficient, and I am hopeful there is a better way to do it, but I'm guessing you can implement a trigger to validate before every insert.

CREATE FUNCTION ticket_choices_insert()
  RETURNS trigger
  LANGUAGE plpgsql
AS $BODY$
DECLARE
  ticket_please integer;
BEGIN

  select count (*)
  into ticket_please
  from ticket_choices tc
  join tickets t on tc.ticket_id = t.id
  join tickets t2 on t.user_id = t2.user_id
  where tc.choice_code = NEW.choice_code and t2.id = NEW.ticket_id;
  
  if ticket_please > 0 then
    return null;
  else
    return NEW;
  end if;
  
END;
$BODY$;

CREATE TRIGGER ticket_choices_insert_trigger
BEFORE INSERT ON ticket_choices
FOR EACH ROW EXECUTE PROCEDURE ticket_choices_insert();

I wonder if a better option might be to denormalize the ticket_choices table and just add the user_id as a derived field. Then it seems the unique constraint would work out of the box.

edited Mar 16, 2021 at 9:03

answered Mar 15, 2021 at 21:32

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

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

3 Comments

Redgren Grumbholdt Over a year ago

But the second option/entry should be accepted, because: first set => [abc, xyz] second set: => [xyz] these are all unique according to what I want. But repeating the same set for a user, it should then throw an error.

Hambone Over a year ago

Oops, sorry. Can you give a sample row that you want to throw the exception?

Redgren Grumbholdt Over a year ago

I have put more examples on the question

Collectives™ on Stack Overflow

Postgres - Trigger to prohibit same records (linked - as a set)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related