10

I want to know if composite primary keys is a bad practice and if not, on which scenarios is recommendable to use.

My question is based on this article

databases design mistakes

The part about composite primary keys:

Bad Practice No. 6: Composite Primary Keys

This is sort of a controversial point, since many database designers talk nowadays about using an integer ID auto-generated field as the primary key instead of a composite one defined by the combination of two or more fields. This is currently defined as the “best practice” and, personally, I tend to agree with it.

Image of a composite primary key

However, this is just a convention and, of course, DBEs allow the definition of composite primary keys, which many designers think are unavoidable. Therefore, as with redundancy, composite primary keys are a design decision.

Beware, though, if your table with a composite primary key is expected to have millions of rows, the index controlling the composite key can grow up to a point where CRUD operation performance is very degraded. In that case, it is a lot better to use a simple integer ID primary key whose index will be compact enough and establish the necessary DBE constraints to maintain uniqueness.

closed as primarily opinion-based by Erik Darling, mustaccio, SqlWorldWide, LowlyDBA, Marco Oct 23 '17 at 9:24

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    This isn't a "good" or "bad" practice. Every design decision must serve a purpose; if you can explain (to yourself and others) why you need a composite PK, you're good to go. Conversely, if you can explain why you don't need it, you're good to go as well. The article you link to does a very poor job explaining, in my view. – mustaccio Oct 21 '17 at 15:06
  • this article signal a point, but if we look popular frameworks (like rails for example) in its "best practices" doesn't support this type of primary keys, so I asked why? it's for technical difficulties or something else. – hackvan Oct 21 '17 at 15:16
  • It is easier for the framework designs to just support "simple" single column integer primary keys. And since most developers (at least in my personal experience) don't have much in terms of database skills (relative to users of this site, at least), it works well enough for most users of the software. Since most users of the software don't need composite keys (or don't think they need at, at least at the start), then they can get away with not providing (good) support for composite keys. – Willem Renzema Oct 21 '17 at 17:44
  • 1
    How is a GUID better than an INTEGER [Serial | Auto_Increment | Identity | <whatever_integer_you_like> ]? – Vérace Oct 21 '17 at 18:55
  • 3
    I would not hire that author – paparazzo Oct 21 '17 at 20:18
25

To say that the use of "Composite keys as PRIMARY KEY is bad practice" is utter nonsense!

Composite PRIMARY KEYs are often a very "good thing" and the only way to model natural situations that occur in everyday life!

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_id SERIAL,
  course_year SMALLINT NOT NULL,
  course_name VARCHAR (100) NOT NULL,
  CONSTRAINT course_pk PRIMARY KEY (course_id)
);


CREATE TABLE student
(
  student_id SERIAL,
  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 linking, many-to-many or 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 course_student
(
  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 this 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 CREATE TABLE -- It can be done either way. I prefer to have everything in the CREATE TABLE statement.


ALTER TABLE course_student 
ADD CONSTRAINT course_student_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 course_student 
ADD CONSTRAINT course_student_sc_uq  
UNIQUE (cs_student_id, cs_course_id);

There is no silver bullet for adding indexes - they will make INSERTs and UPDATEs slower, but at the great benefit of hugely 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!

  • 1
    in the example of course and students, it's possible for course_student have an id as primary key and a unique index on cs_student_id cs_course_id and have the same results? – hackvan Oct 24 '17 at 18:17
  • 1
    Why waste resources doing that? With PK(course_id, student_id), by definition you already have a unique index on those fields! A unique index on (student_id, course_id) might be of use in speeding up searches - say if you were looking for students who were not taking any courses, but that decision could be an operational one, but in these days of relatively cheap storage, I would recoomend it, especially since one would think that the table isn't going to be updated very frequently. – Vérace Oct 24 '17 at 21:18
  • Agree completely for link tables - I'm working with several right now. However, when I put my C# hat on, I'm working with reversepoco generator and building out useful classes (find, save etc) for the next layer up. I hit a major problem - composite keys become a PITA for having any generic save/find code. Yes, maybe I could go back to EDMX files but I still need to work around either special case code (count Pkey columns?) or add artificial surrogate key (don't like and need additional uniqueness constraints :(). So, I guess folks not like composites are speaking from App layer code. – Richard Griffiths Jul 4 '18 at 10:28
  • Depending on the frequency of inserts and the frequency of index defragmenetaion vs maintenance window this is the better solution. But some design choices are compromises driven by requirements that may not be immediately visible. But as one comment said, identify pros/cons of both scenarios and make a design choice. – Jonathan Fite Apr 17 at 12:16
  • What happens when a student repeats the course? Then unless courses separated in time get different ids - then you have yet another mapping table. Or add a field for course date which now must be added to the key. – iheanyi Jul 15 at 20:32
2

My half-educated take: a "primary key" doesn't have to be the only unique key used to look up data in the table, although data management tools will offer it as default selection. So for choosing whether to have a composite of two columns or a random (probably serial) generated number as the table key, you can have two different keys at once.

If data values include a suitable unique term that can represent the row, I'd rather declare that as "primary key", even if composite, than use a "synthetic" key. The synthetic key may perform better for technical reasons, but my own default choice is to designate and use the real term as primary key, unless you really need to go the other way to make your service work.

A Microsoft SQL Server has the distinct but related feature of the "clustered index" that controls the physical storage of data in index order, and also is used inside other indexes. By default, a primary key is created as a clustered index, but you can choose non-clustered instead, preferably after creating the clustered index. So you can have an integer identity generated column as clustered index, and, say, file name nvarchar(128 characters) as primary key. This may be better because the clustered index key is narrow, even if you store the file name as the foreign key term in other tables - although this example is a good case for also not doing that.

If your design involves importing tables of data that include an inconvenient primary key to identify related data, then you're pretty much stuck with that.

https://www.techopedia.com/definition/5547/primary-key describes an example of choosing whether to store data with a customer's social security number as the customer key in all the data tables, or to generate an arbitrary customer_id when you register them. Actually, this is a grave abuse of SSN, aside from whether it works or not; it is a personal and confidential data value.

So, an advantage of using a real-world fact as the key is that without joining back to the "Customer" table, you can retrieve information about them in other tables - but it's also a data security issue.

Also, you're in trouble if the SSN or other data key was recorded incorrectly, so you have the wrong value in 20 constrained tables instead of in "Customer" only. Whereas the synthetic customer_id has no external meaning so it can't be a wrong value.

Not the answer you're looking for? Browse other questions tagged or ask your own question.