Creating a unique constraint on two columns togeth

问题:

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)
)

?

回答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

回答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.

回答3:

unique_index 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 is what you want, though.