Wanted to double check something regarding composite keys in cross reference tables, those we define when creating a many to many relationship in a database.
in this lessons example: https://bit.ly/3VUFIIE , we create these tables to demonstrate properly implementing a schema from the start. This was the schema:
Hope the above renders properly.
In this picture from the lesson, the bottom right is the cross reference table, referencing both PRIMARY KEYS in tables: majors and students. 1 student can take many majors, a major can be taken by many students hence a * to * relationship.
Its my understanding that this table should also have either 1) a unique primary key to represent each row in this cross-reference table (i can’t think of something suitable), or 2) a composite primary key combining both FOREIGN KEYS student_id and major_id which collectively will uniquely identify each row.
I think the composite key would be more beneficial as both FKs on their own don’t uniquely identify a row. however this was never implemented in this codecademy lesson, so my question is: is there a scenario to avoid putting any unique identifiers in a cross reference table?