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).

Because we don’t want to see ugly SQL errors in our Rails application, we can also add a validation:

class Membership < ApplicationRecord
  belongs_to :user
  belongs_to :group
  belongs_to :clan

  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!