Hi, please. I have a question regarding migrations in Ecto. But maybe this more of a PostgreSQL question rather than an Elixir one.
I have a many-to-many
relation between students and teachers. One student can have many teachers and the same teacher can have many students.
So, I’m basically defining my migration like this:
def change do
create table(:students_teachers, primary_key: false) do
add :id, :binary_id, primary_key: true
add :criteria, :integer, null: false
add(:student_id, references(:students, on_delete: :delete_all, type: :binary_id),
null: false
)
add(:teacher_id, references(:teachers, on_delete: :delete_all, type: :binary_id),
null: false
)
end
create index(:students_teachers, [:student_id])
create index(:students_teachers, [:teacher_id])
end
Ok, my doubt is about the last line of the code, the part where I define the indexes:
create index(:students_teachers, [:student_id])
create index(:students_teachers, [:teacher_id])
But if I do it this in a second way create index(:students_teachers, [:student_id, :teacher_id])
something is gonna change.
If I do a diff
to the two \d students_teachers
generated tables in PostgreSQL with the different types of migrations I will see that there are some changes.
This the output of my diff (which BTW only sees the difference in the index definition):
> "students_teachers_student_id_index" btree (student_id)
> "students_teachers_teacher_id_index" btree (teacher_id)
---
< "students_teachers_student_id_teacher_id" btree (student_id, teacher_id)
So, my question is:
How do this two approaches change the way my data is related? What is the difference here? How do the teacher and the student are related according to the different ways I migrated the database?