When to implement Composite Primary Key in cross reference table

Get Help SQL
,
#1

Hello,

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:

image

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?

#2

i think i answered my own question, something unique would be needed to prevent duplication in the junction / reference table describing the many to many relationship, assuming there was a tight policy preventing students from enrolling multiple times - like a composite primary key.

In the real world, i think this would be a incomplete schema either way - even if a user could enroll twice, it would be useful to have a unique date stamp column to ensure no two rows are the same.