Assoc composite foreign key support

[perezd]

I have the following tables:

CREATE TABLE locations (
  state VARCHAR(2) NOT NULL,
  zip VARCHAR(5) NOT NULL,
  id INT8 NOT NULL,
  name VARCHAR(255) NOT NULL,
  CONSTRAINT "primary" PRIMARY KEY (state ASC, zip ASC, id ASC),
  FAMILY "primary" (state, zip, id, name)
)

CREATE TABLE addresses (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  line_1 VARCHAR(255) NOT NULL,
  line_2 VARCHAR(255) NULL,
  line_3 VARCHAR(255) NULL,
  city VARCHAR(255) NOT NULL,
  state VARCHAR(2) NOT NULL,
  zip VARCHAR(5) NOT NULL,
  inserted_at TIMESTAMP(0) NOT NULL,
  updated_at TIMESTAMP(0) NOT NULL,
  CONSTRAINT "primary" PRIMARY KEY (id ASC),
  FAMILY "primary" (id, line_1, line_2, line_3, city, state, zip, inserted_at, updated_at)
)

CREATE TABLE locations_addresses (
  state VARCHAR(2) NOT NULL,
  zip VARCHAR(5) NOT NULL,
  id INT8 NOT NULL,
  address_id UUID NOT NULL,
  CONSTRAINT "primary" PRIMARY KEY (state ASC, zip ASC, id ASC, address_id ASC),
  CONSTRAINT fk_locations FOREIGN KEY (state, zip, id) REFERENCES locations(state, zip, id),
  CONSTRAINT fk_addresses FOREIGN KEY (address_id) REFERENCES addresses(id),
  INDEX locations_addresses_auto_index_fk_addresses (address_id ASC),
  FAMILY "primary" (state, zip, id, address_id)
) INTERLEAVE IN PARENT locations (state, zip, id)

I need to be able to designate a many_to_many relationship where one side of the relationship is a composite foreign key. In my Location schema, I expected to be able to define the following:

    many_to_many :addresses, Address,
      join_through: "locations_addresses",
      join_keys: [location_id: [:state, :zip, :id], address_id: :id]

but this returns the following error:

** (ArgumentError) many_to_many :addresses expect :join_keys to be a keyword list with two entries, the first being how the join table should reach the current schema and the second how the join table should reach the associated schema. For example: [location_id: :id, address_id: :id]

Rationale

I'm attempting to use CockroachDB's parent interleaving functionality which requires that interleaving tables use composite primary keys to establish a hierarchical relationship. More on that here:
https://www.cockroachlabs.com/docs/stable/interleave-in-parent.html

[josevalim]

We don’t support composite foreign keys at the moment and we have no plans to. If someone submits a pull request, we will gladly review it, but it is not in our roadmap.thanks.

[perezd]

I'd be willing to take a stab at it, do you have any recommendations/pointers about how one might get familiar enough with the codebase to make a contribution like that?

[josevalim]

Most the code will be in Ecto.Associations, the code there tends to be well contained, but it will certainly be a large change.

[josevalim]

I will close this one for now, as we don't plan to tackle it ourselves, but if you hvae any follow up question feel free to ask it here and I will be glad to answer.

[seivan]

@josevalim Is this still valid?
This part of the documentation gives the impression they are supported.

we define post_id+org_id as a composite foreign key

[josevalim]

@seivan you can define them as composite foreign keys in the database level but Ecto uses a single foreign for queries, you need to pass the other one as parameter (which is quite straightforward).

[seivan]

@josevalim Ah! Just to be clear you’re referring to things like Ecto.assoc, Repo.preload, Changeset.cast_assoc and everything else that uses some reflection over key attributes for generating convenience?

[hickscorp]

Quite curious here - we have a database that defines primary keys as [:id, :version].
Foreign keys are declared in Ecto migrations such as (more or less):

      add :version, :string
      add :something_id,
          references(:something, type: :uuid, with: [version: :version]),
          null: true

That's all nice and dandy, but then in Ecto Schemas, we cannot seem to find how to indicate that foreign keys are composite...
Is there any progress on this? Somehow, I'm looking for similar options for association declarations (Eg with: ... in combination with foreign_key: ... maybe? Where we can map the local version to the other side of the association's version) but can't seem to find anything.