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
num_nonnulls() allows us to count how many are
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!