PostgreSQL - enforce only one column filled via constraint

Recently I got a task, that required me to ensure that of two columns, either none, or only one get filled. That should be ensured on the database level. In my #ruby code it would be easy to add a validation to check for this, but as we are doing manual inserts into the DB, we need to also make sure, that the database enforces this constraint.

Luckily, postgres has a feature for us, that was introduced in version 9.6, num_nulls() allows us to count how many values in a list are NULL and num_nonnulls() allows us to count how many are NOT NULL.

SELECT num_nulls(NULL, 1, 'hello', TRUE);
# => 1

SELECT num_nonnulls(NULL, 1, 'hello', TRUE);
# => 3

We can combine this into a constraint, that gets run on our table, when a new row is inserted. Let’s create a table that creates a membership between a user and either a group or a clan, but only one of those two:

CREATE TABLE memberships (
  user_id BIGINT,
  group_id BIGINT,
  clan_id BIGINT
);

ALTER TABLE memberships
  ADD CONSTRAINT only_membership_in_clan_or_group
  CHECK (num_nonnulls(group_id, clan_id) = 1);

Now, we can enter values, where only one of the two columns is filled:

INSERT INTO memberships (user_id, group_id, clan_id)
  VALUES (1, 2, NULL);
# => INSERT 0 1

But if we try to fill both, we get an error:

INSERT INTO memberships (user_id, group_id, clan_id)
  VALUES (1, 2, 3);
# => ERROR:  new row for relation "memberships" violates check constraint "only_membership_in_clan_or_group"
#    DETAIL:  Failing row contains (1, 2, 3).

But wait, why not make it polymorphic?

The “normal” approach to a problem like this in Rails would be to make the association polymorphic, and add a member_of_type and member_of_id column and then insert "Clan", 7 or "Club", 42 into the fields respectively. We can even enforce only certain types by making the type-column an ENUM and enforcing them. But with this approach we are losing a bunch of niceities the database offers:

• With such a polymorphic association approach we cannot ensure referential integrity using foreign keys
• Adding JOINS can become really painful

There are points, where we definetly should use polymorphic relations. But in some cases, especially those where we need to ensure referential integrity, it might be the better solution to add two seperate foreign key columns and use the constraint to ensure only one is filled.

Back to our example:

Now we have everything ensured in the database and inserting a violating record would raise an SQL Exception. Because we don’t want to see those ugly SQL errors in our Rails application, we can also add a validation:

class Membership < ApplicationRecord
  belongs_to :user
  belongs_to :group, optional: true
  belongs_to :clan, optional: true

  validates :clan_xor_group

  private

  def clan_xor_group
    return if clan ^ group

    errors.add :base, "you can only be a member of a clan OR a group"
  end
end

Happy constraint enforcing!