Creating a unique constraint on two columns together in Ecto

Question

How do you create a unique index on two columns in Ecto, which would correspond to this:

CREATE TABLE someTable (
    col1 int NOT NULL,
    col2 int NOT NULL,
    primary key (col1, col2)
)

?

Answer 1

A Little follow up on Patrick's answer

Using only create unique_index on your model will ultimately throw an exception instead of giving you an error.

To get an error add a constraint on your changeset but as a paremeter you can give the index name created by unique_index.

So in your migration file :

create unique_index(:your_table, [:col1, :col2], name: :your_index_name)

Then in your changeset :

def changeset(model, param \\ :empty) do
  model
  |> cast(params, @required_fields, @optional_fields)
  |> unique_constraint(:name_your_constraint, name: :your_index_name)
end

Answer 2

You can create a unique index across multiple rows with

create unique_index(:some_table, [:col1, :col2])

I suppose if you want to have composite keys, you need to use execute/1 to run your SQL manually. Not sure how well composite keys work with Ecto though, I generally just stick with the standard serial id per table.

If you should go with the composite key approach, I think that the NOT NULL constraints are not necessary. The composite key should already enforce that the columns are not null.

Answer 3

unique_index/3 would not create a composite primary key as is shown in the question's example. It does create a unique constraint.

If you do want to create a composite primary key (note: not recommended when working with Ecto), there's more information here:

Migration:

defmodule HelloPhoenix.Repo.Migrations.CreatePlayer do
  use Ecto.Migration

  def change do
    create table(:players, primary_key: false) do
      add :first_name, :string, primary_key: true
      add :last_name, :string, primary_key: true
      add :position, :string
      add :number, :integer
      ...

Schema:

defmodule HelloPhoenix.Player do
  use Ecto.Schema

  @primary_key false
  schema "players" do
    field :first_name, :string, primary_key: true
    field :last_name, :string, primary_key: true
    field :position, :string
    field :number, :integer
    ...

In most cases unique_index/3 is what you want, though.

Answer 4

This is an old post, but I ran into this today. Remember that a unique constraint is not the same as a unique index (at least, not in all versions of PostGres -- there seems to be some movement on this). In Ecto, it seems that currently the only way to add a constraint that is not an index is to use execute/1 in your migration, e.g.

defmodule YourMigration do
  use Ecto.Migration

  def change do
    execute(
      "ALTER TABLE pre.yourtable ADD CONSTRAINT something UNIQUE (something)"
    )
  end
end