0

I handle members' roles in a table with this structure:

  • id: id of the row
  • id_member: integer, foreign key is 'id' column in 'members' table
  • id_role: integer, foreign key is 'id' column in 'roles' table
  • date_start: timestamp when this user gets the role
  • date_end: timestamp when this user loses the role

When I add a role, the date_start is set with current_timestamp, and date_end is null. When I remove a role, the date_end is set with current_timestamp.

I don't want a user to have several roles at the same time, so initially I thought about setting a triple primary key: id_member, id_role and date_end, but it appears I can't put a nullable column as primary key.

How could I change the structure of the table so that I can prevent a user having 2 active roles? I thought about adding a active column but not only would it overcharge the structure, but also I won't be able to save 2 historical roles (if a user was ROLE3 during 4 different periods, for example).

Thanks in advance.

djcaesar9114
  • 1,880
  • 1
  • 21
  • 41
  • 2
    Exclusion constraint with a tstzrange column: https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRAINT – Mike Organek Sep 16 '21 at 21:49
  • I see the documentation, but how could I alter my table? I don't see how I can say "I don't want to have 2 same triplets `id_member`, `id_role` and `date_ned` === NULL – djcaesar9114 Sep 16 '21 at 21:54
  • 1
    What about using an unique index instead of the primary key? you could have these columns as your unique index. – William Andrés Bernal Sep 16 '21 at 21:58
  • After verification, it doesn't do the trick. I can still add the same triplets. – djcaesar9114 Sep 16 '21 at 22:20
  • But you led me to a solution: https://stackoverflow.com/questions/23449207/postgres-unique-constraint-not-enforcing-uniqueness/23449309 Thanks. You can add your answer so that I can vote for it if you want. – djcaesar9114 Sep 16 '21 at 22:23
  • Rather that defaulting the end_date to null set the default to `'infinity'::date`2. As a benefit when determining active dated the `between date_start and date_end` works, and satisfactory for PK. – Belayer Sep 19 '21 at 20:24

1 Answers1

2

I don't want a user to have several roles at the same time

Partial UNIQUE index

So, each member can only have a single active role (date_end IS NULL).
A partial UNIQUE index will enforce that:

CREATE UNIQUE INDEX tbl_member_active_role_uni ON tbl (id_member)
WHERE date_end IS NULL;  -- active role

See:

EXCLUDE

The above still allows to add historic entries that overlap. To disallow that, too, use an exclusion constraint. You'll need the additional module btree_gist for your integer column. See:

Then:

ALTER TABLE tbl ADD CONSTRAINT tbl_member_no_overlapping_role
EXCLUDE USING gist (id_member with =, tsrange(date_start, date_end) WITH &&);

NULL values for date_end happen to work perfectly. In a range types, NULL as upper bound signifies "unbounded". See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228