Does Ecto supports composite foreign keys

Questions / Help
6

For references.
Below are steps that I used to accomplish using composite keys functionality.

First I do my migrations using the raw sql in the migration like below to make sure that all foreign keys are created properly. The code are copied from my code.

defmodule Stock.Repo.Migrations.CreateChange do
  use Ecto.Migration

  def up do
    execute """
      create table changes(
      company_initial varchar(1) not null,
      product_roll varchar(255) not null,
      product_code varchar(255) not null,
      changes numeric not null,
      primary key (company_initial, product_roll, product_code)
      );
    """
    execute """
      alter table changes
      add constraint changes_products_fk
      foreign key (company_initial, product_roll, product_code)
      references products(company_initial, roll, code)
    """
  end

  def down do
    execute "drop table changes;"
  end
end

Then in my model code I did not use the has_many or belongs_to. You can see that the use of @primary_key false and setup of primary_key: true on the fields. I only use the constraints to trap the failing inserting and updating columns from sql and to present it as error.

defmodule Stock.Change do
  use Stock.Web, :model

  @primary_key false
  schema "changes" do
    field :company_initial, :string, primary_key: true, size: 1
    field :product_code, :string, primary_key: true
    field :product_roll, :string, primary_key: true
    field :changes, :decimal
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:changes, :company_initial, :product_code, :product_roll])
    |> validate_required([:changes, :company_initial, :product_code, :product_roll])
    |> unique_constraint(:company_initial, name: "changes_products_fk")
    |> unique_constraint(:company_initial, name: "changes_pkey")
    |> foreign_key_constraint(:company_initial, name: "changes_products_fk")
  end
end

–
Dev.

2 Likes