Unique constraint with null values

jmurphyweb

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.

9 comments

#ecto #postgres #migrations

0 4896 9

2025-05-15 13:01:21 UTC

First 9 of 9 Posts

chrismcg

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.

Post #1

LostKobrakai

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, ''));" )

Post #2

jmurphyweb

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!

Post #3

jmurphyweb

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

Thanks for your help!

Post #4

jmurphyweb

@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

Post #5

LostKobrakai

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

Post #6

jmurphyweb

Perfect! thanks for your help

Post #7

j_uffel

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