A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table.


But what happens if both columns have the same value. Can it still uniquely identify the row in the table. Would I need to choose a 3rd column for the composite key. Or is it better to make a primary key that that can just uniquely identify the row.

Ex. a student retaking the same course again because he fail.

When would both columns have the same value? If that happens, it seems like your design is faulty in the first place.

STU_ID AND COURSE_ID is the composite key. If a student fail last year and register again for this year, there would be same entry again.

Right, then you do need all three columns as your composite key.

in this case, would it be better to just use a surrogate key.

wouldn't it make referencing to another table easier.

Surrogate keys make it such that your database is no longer normalized (because the key is not actually part of the data, you can therefore have effectively duplicate rows).

Composite keys make it such that this is not possible, keeping your database in normal form.