To say that the use of
"Composite keys as PRIMARY KEY is bad practice" is utter nonsense!
PRIMARY KEYs are often a very "good thing" and the only way to model natural situations that occur in everyday life! Having said that, there would also be many situations where using a composite PK would be cumbersome and unwieldy and therefore not an optimal choice.
Your question is:
"if composite primary keys are bad practice... (answered)
and if not, in which scenarios is their use beneficial?".
Below is an example of where composite keys represent a rational/beneficial choice as the PK (indeed the only rational one as I see it - in the fiddle here, there's an extra example of having grades also!
On the plus side for composite keys, think of the classic Databases-101 teaching example of students and courses and the many courses taken by many students!
Create tables course and student:
CREATE TABLE course
course_year SMALLINT NOT NULL,
course_name VARCHAR (100) NOT NULL,
CONSTRAINT course_pk PRIMARY KEY (course_id)
CREATE TABLE student
student_name VARCHAR (50),
CONSTRAINT student_pk PRIMARY KEY (student_id)
I'll give you the example in the PostgreSQL dialect (and MySQL) - should work for any server with a bit of tweaking.
Now, you obviously want to keep track of which student is taking which course - so you have what's called a
joining table (also called
m-to-n tables). They are also known as
associative entities in more technical jargon!
1 course can have many students.
1 student can take many courses.
So, you create a joining table
CREATE TABLE registration
cs_course_id INTEGER NOT NULL,
cs_student_id INTEGER NOT NULL,
-- now for FK constraints - have to ensure that the student
-- actually exists, ditto for the course.
CREATE CONSTRAINT cs_course_fk FOREIGN KEY (cs_course_id)
REFERENCES course (course_id),
CREATE CONSTRAINT cs_student_fk FOREIGN KEY (cs_student_id)
REFERENCES student (student_id)
Now, the only way to sensibly give the
registration table a
PRIMARY KEY is to make that
KEY a combination of course and student. That way, you can't get:
a duplicate of student and course combination
a course can only have the same student enrolled once, and
a student can only enroll in the same course one time only
you also have a ready made search
KEY on course per student - AKA a covering index,
it is trivial to find courses without students and students who are taking no courses!
-- The db-fiddle example has the PK constraint folded into the
-- It can be done either way. I prefer to have everything in the
CREATE TABLE statement.
ALTER TABLE registration
ADD CONSTRAINT registration_pk
PRIMARY KEY (cs_course_id, cs_student_id);
Now, you could, if you were finding that searches for student by course were slow, use a
UNIQUE INDEX on (sc_student_id, sc_course_id).
ALTER TABLE registration
ADD CONSTRAINT course_student_sc_uq
UNIQUE (cs_student_id, cs_course_id);
There is no silver bullet for adding indexes - they will make
UPDATEs slower, but at the great benefit of greatly decreasing
SELECT times! It's up to the developer to decide to index given their knowledge and experience, but to say that composite
PRIMARY KEYs are always bad is just plain wrong.
In the case of joining tables, they are usually the only
PRIMARY KEY that make sense! Joining tables are also very frequently the only way of modelling what happens in business or nature or in virtually every sphere I can think of!
This PK is also of use as a
covering index which can help speed up searches. In this case, it would be particularly useful if one were searching regularly on (course_id, student_id) which, one would imagine, can often be the case!
This is just a small example of where a composite
PRIMARY KEY can be a very good idea, and the only sane way to model reality! Off the top of my head, I can think of many many more.
An example from my own work!
Consider a flight table containing a flight_id, a list of departure and arrival airports and the relevant times and then also a cabin_crew table with crew members!
The only sane way this can be modelled is to have a flight_crew table with the flight_id and the crew_id as attibutes and the only sane
PRIMARY KEY is to use the composite key of the two fields!