Unique constraint with null values

Hi all,

I’ve added a unique constraint across all columns of my project_roles table, which successfully prevents a row being added with all 4 columns filled. However, it’s not playing nicely when one of my values is null.

    create table(:project_roles, primary_key: false) do
      add :user_id, :integer, null: false
      add :role_id, :integer, null: false
      add :project_id, :integer, null: false
      add :department_id, :integer
    end

    create unique_index(
      :project_roles,
      [:user_id, :project_id, :department_id, :role_id],
      name: :unique_user_department_role
    )

Here’s the constraint:

The constraint successfully prevents the following map being added twice:

    %{
      user_id: 1,
      role_id: 1,
      project_id: 1,
      department_id: 1,
    }

But it allows this maps to be added twice:

    %{
      user_id: 1,
      role_id: 1,
      project_id: 1,
      department_id: nil,
    }

Ideally we would have it fail for multiple entries of that second example.