Search code examples
postgresqlconstraintsprimary-key

Simulate a primary key for a nullable column

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.

Solution

  • 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: