Unique constraint with null values

Questions / Help
, ,
1

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:

32
32751×58 5.11 KB

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.

2

The SQL standard specifies that NULL != NULL as NULL represents an unknown value. If you don’t know what the value is you can’t say what it is or isn’t equal too. So I’m pretty sure as far as the DB is concerned your last example is not unique.

3

You can create indexes with coalesce’d values like so:

execute( "create unique index INDEX_NAME on TABLE (user_id, role_id, project_id, coalesce(department_id, ''));" )
8 Likes
4

Thanks for your response. On doing a google about my issue it seemed that people wanted to allow nulls to slip through the constraint, but given that they were already slipping through for me, i thought that I was seeing something other than the standard.

Your explanation makes sense though, cheers!

5

Very handy, not sure I would have ever found out about this.

Thanks for your help!

6

@LostKobrakai we’ve just tried that but get an error. Same error regardless of what we put the default in for the coalesced column.

Do you have any thoughts?

Migration:

defmodule Ev2.Repo.Migrations.UpdateProjectRoleConstraint do
  use Ecto.Migration

  def up do
    execute("DROP INDEX IF EXISTS unique_user_department_role")

    execute(
      "CREATE UNIQUE INDEX unique_user_department_role ON project_roles (user_id, role_id, project_id, coalesce(department_id, -1)"
    )
  end
end

Produces the following error.

[info] == Running Ev2.Repo.Migrations.UpdateProjectRoleConstraint.up/0 forward
[info] execute "DROP INDEX IF EXISTS unique_user_department_role"
[info] execute "CREATE UNIQUE INDEX unique_user_department_role ON project_roles (user_id, role_id, project_id, coalesce(department_id, -1)"
** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at end of input
    (ecto) lib/ecto/adapters/sql.ex:200: Ecto.Adapters.SQL.query!/5
    (ecto) lib/ecto/adapters/postgres.ex:96: anonymous fn/4 in Ecto.Adapters.Postgres.execute_ddl/3
    (elixir) lib/enum.ex:1925: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/adapters/postgres.ex:96: Ecto.Adapters.Postgres.execute_ddl/3
    (ecto) lib/ecto/migration/runner.ex:104: anonymous fn/2 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1925: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/migration/runner.ex:102: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:181: :timer.tc/2
    (ecto) lib/ecto/migration/runner.ex:26: Ecto.Migration.Runner.run/6
    (ecto) lib/ecto/migrator.ex:128: Ecto.Migrator.attempt/6
    (ecto) lib/ecto/migrator.ex:72: anonymous fn/4 in Ecto.Migrator.do_up/4
    (ecto) lib/ecto/adapters/sql.ex:576: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
    (db_connection) lib/db_connection.ex:1283: DBConnection.transaction_run/4
    (db_connection) lib/db_connection.ex:1207: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:798: DBConnection.transaction/3
    (ecto) lib/ecto/migrator.ex:261: anonymous fn/4 in Ecto.Migrator.migrate/4
    (elixir) lib/enum.ex:1314: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/mix/tasks/ecto.migrate.ex:83: anonymous fn/4 in Mix.Tasks.Ecto.Migrate.run/2
    (elixir) lib/enum.ex:765: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:765: Enum.each/2
7

It might be just the one closing ) missing in the end.

8

Perfect! thanks for your help :bowing_man:

9

For anyone else stumbling upon this old thread, you can also use postgres’ NULLS NOT DISTINCT option for unique indices to achieve a similar thing without explicitly coercing values (postgres documentation)

EDIT: actually this is built into Ecto.Migration already! Thanks @BrightEyesDavid for pointing out :folded_hands:

10

Details on the Ecto Migration nulls_distinct option here.

2 Likes