Referencing a table through a composite foreign key throws column "id" does not exist

I’m honestly just kinda torn between using auto_generated ids for all tables (which eliminates any kind of database integrity) or using composite primary keys and foreign keys (which isn’t what ecto wants you to do).

Here it says that ecto doesn’t want you to go through the path of creating composite primary keys:

Note: While Ecto allows us to do this, it’s not the natural path Ecto wants to take. Allowing Ecto to use an auto-incremented integer is definitely the right way to go for new applications.

But how can I ensure that the database follows proper integrity restrictions, then? Should I have the auto-generated pk as part of the foreign key? Wouldn’t that introduce redundancy in the database, too, since we’re creating a foreign key that contains a subset that could also be a foreign key (the fk itself without the auto-generated id)?

I am getting lost rn; I’d appreciate any kind of help.

I have thought a bit more about it, and I have come to the conclusion that what Ecto wants us to do is to use the autogenerated primary key for joining tables and the foreign keys defined on the tables purely as an integrity mechanism, not for joining tables

This is just completely different from what I was taught, I was told that PKs should be composite and as minimal as possible when working with tables that require them and to always store those PK fields in tables as FKs and also use the FKs to join the 2 tables. And now it seems that I need to store both the autogenerated PK and the FK fields in the goal table and use the PK to join tables and the FK for integrity restrictions.

Is this correct?

I was also confused when I was first thinking about composite keys in ecto. It certainly is possible to define these relations in your migration.

But keep in mind that ecto does not allow you to take advantage of assocs (using cast_assoc, put_assoc, preload, etc.) in combination with composite FKs. That was a design decision (I think). I learned that through a github comment of José, somewhere, but I forgot where exactly. Would love if someone can confirm, or state the opposite if I’m wrong.

In the guides there is an interesting page about multitenancy setups, that explains an interesting technique to tighten the constraints, when taking into account an org_id for each table. Might give you some idea’s. Multi tenancy with foreign keys — Ecto v3.14.0

I see youre defining a composite PK, and then also define a unique constraint. Thats not required afaik, since the PK definition implies an index already.

Would love to hear from others how they tighten down the db models, leveraging referential constraints, in comination with ecto.

create table("user_scope", primary_key: false) do
  add(:user_id, references(:user, on_delete: :delete_all), primary_key: true)
  add(:scope_id, references(:scope, on_delete: :delete_all), primary_key: true)
end


create table("course_enrollment", primary_key: false) do
  add(:user_id, :id, primary_key: true)
  add(:scope_id, references(:user_scope, with: [user_id: :user_id], column: :scope_id, on_delete: :delete_all), primary_key: true)
  add(:course_id, references(:course, on_delete: :delete_all), primary_key: true)

  # do you need this column?
  # add(:user_scope_id, references(:user_scope, with: [user_id: :user_id, scope_id: :scope_id], on_delete: :delete_all), null: false)

  add(:active, :boolean, default: true)
  timestamps()
end

Does this work? I’m on mobile, so I can’t check. The gist is that your fourth column of the enrollment table seems redundant, and the FK constraint can be constructed with the first two columns (the first is just a plain column, this is where your version fails), and the the second column also defines the composite FK.
Could be that I’m missing nuance from your code snippet…

Would you mind to explain what does the column field in the references function do? Wouldn’t it be able to appear in the user_id row as well? And how does this affect the scope_id field in course_enrollment? Does it become a foreign key while also becoming a field? I don’t understand it.

Edit: fix quote

The add function call makes sure there is a column. While the nested references call ensures one FK constraint. By default a FK references the :id column. But since that’s not what you want, you need to specify it manually. That’s what your first error was all about: it tried to reference a column that didn’t exist.

I now notice that you were referencing even more tables from the enrollment table. But that’s actually redundant, if the scope_id and user_id already reference the enrollment columns, which in their turn reference the user and scope table.

You can inspect your db to see if this migration does the things you want it to do. You can try to insert something that offends the constraints, in raw sql, to see it working.

But remeber that you won’t be able to get assoc’s to work automatically, with a composite FK.

Did the suggestions ever fly? I’m curious…