CODEWITHSUNDEEP
postgresqlconstraintsprimary-key
djcaesar9114
djcaesar9114

Reputation: 2137

Simulate a primary key for a nullable column

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

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.

Upvotes: 0

Views: 583

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657202

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:

Upvotes: 2

Related Questions