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?