When is a composite key the right choice for a primary key?
When the row is identified by two things together and by nothing else.
So the test is this. Ask what makes one row different from another. If the answer is one column, then that column is the key. If the answer is two columns that only work as a pair, then you need a composite key.
CREATE TABLE bookings (
room_id INTEGER NOT NULL,
night TEXT NOT NULL,
guest_email VARCHAR(200),
PRIMARY KEY (room_id, night)
);
There are three good reasons to reach for one. The first one is that the columns that identify the thing are already in the table, so a made up number adds nothing. The second one is that the key says what it means, because anybody who reads the table can see that a room on a night is what identifies a booking. The third one is that there is nothing to keep in step, because the key cannot be got wrong in the way that a number that your program makes up can.
There is one case where a composite key is not the right answer, and that is when the thing has a natural number that is already unique. Then that column is the key, and the composite one is a rule on the side.
The next two questions in this topic ask for a table and for the awkward case.