What is the difference between composite keys and foreign keys?

What is the difference between composite keys and foreign keys?

Composite keys are a combination of two or more columns in a table that uniquely identify a row, while foreign keys are columns that create a link between two tables by referencing the primary key of another table.

Can you give me an example of each?

Sure! A composite key example could be a table of student enrollments where the combination of student_id and course_id uniquely identifies each enrollment. A foreign key example would be a course_id in a student table that references the primary key in a courses table.

Why would I use a composite key instead of just a single primary key?

You would use a composite key when a single column is not sufficient to uniquely identify a record. It helps maintain data integrity and ensures that the combination of values is unique.

Are there any drawbacks to using composite keys?

Yes, composite keys can complicate queries and relationships, as they require multiple columns to be referenced, which can lead to more complex SQL statements.