Does Ecto supports composite foreign keys

Questions / Help
1

I wonder does ecto supports composite foreign keys?

3 Likes
2

Not really.

If your database is using composite foreign keys, Ecto will still work just fine assuming you set both keys when building changesets. Note you can still use the constraint helpers in the changeset to convert database errors into changeset errors.

For queries, you won’t be able to use the built-in queries from associations but you can still build queries that compares across fields.

What Ecto is missing is a convenient syntax for generating composite foreign key in migrations but it can be bypassed by using execute in your migrations to run commands.

So we don’t support it but you do have the tools to make it work.

6 Likes
3

Thank you @josevalim

I am trying this out today.

Dev.

4

[quote=“josevalim, post:2, topic:2466”]
If your database is using composite foreign keys, Ecto will still work just fine assuming you set both keys when building changesets. Note you can still use the constraint helpers in the changeset to convert database errors into changeset errors.[/quote]

Am I understanding correct here -
“Setting both keys” you means something like following right? -

table1 schema -

@primary_key false
schema “table1” do
field :p1, :string, primary_key: true
field :p2, :string, primary_key: true
end

table2 schema -
@primary_key false
schema “table2” do
field :t1_p1, :string, :primary_key: true
field :t1_p2, :string, :primary_key: true
end

In the above code. We wouldn’t be able to set belongs_to and has_many. Since when using them they only can references to a single db column. As I can see from this [ecto doc] (Ecto.Schema — Ecto v3.11.1) that ‘references’ can only refers to a single field of another column.

And then after doing that we rely on the failing to insert or update the on the table. (given that sql indexes are properly setup) And then allow the constraint helper to do the work updating the fails to user.

Thank you @josevalim

Dev

5

I tried as above. It worked.
I will write down steps in another reply. Others might find it useful.

Thanks again @josevalim
Dev

2 Likes
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
7

So we don’t support it but you do have the tools to make it work.

This is what I love about Elixir. It has amazing tools for all the base cases but doesn’t limit you from doing something complex if you need to. And if you need to do something complex, it doesn’t make sense to have native support for it. Hopefully this mindset will prevent the tools from getting too bloated and remain an elegant environment to work in.

8

Well except for joining 2+ schema’s, have to drop to raw SQL for that
 :frowning:

9

See, still possible. :stuck_out_tongue:

2 Likes
10

Lol, just without the tasty type-safeness and coercions, I really like the tasty type safeness and coercions. ^.^

11

No, it is all there!

fragment("some_function(?, ?, ?)",
         type(^a, :foo),
         type(^b, :bar),
         type(^c, :baz))
3 Likes
12

Hmm, perhaps
 Still working in stringly SQL though. ^.^;

2 Likes