I have a table called devices. Most of the devices that will get stored in this table can be uniquely identified by their serial number and part number. But there are some device types that do not have serial number and part number assigned to them. Instead they can be uniquely identified by another field (internal id).

Should I create a surrogate key for this table or should I create a composite primary key (serial number, part number, internal id) and insert default values to the serial number and part number columns when they are not supplied? The device types that do not have part number and serial number now, will have the numbers assigned to them in the future releases (may be 5 years from now). Should I create a surrogate key or a composite key in this scenario? Or using the three unique attributes, should I create a hash in the program and use that as a surrogate key for the tables?

  • 1
    This question has asome good details about surrogate performance vs natural keys.
    – Hannah Vernon
    Commented Aug 26, 2017 at 16:33
  • Most serial numbering or inventorying systems do not glob Quants (item + qty) together with serialized items (product/part code) in the same table.
    – StingyJack
    Commented Aug 7, 2019 at 13:51

2 Answers 2


Use the surrogate key as the primary key for the moment. When natural keys become available, make them non-nullable unique constraints.

By the YAGNI principle, you should only code for "real-life" current requirements - a primary key that may (or may not) arrive in 5 years is not worth considering now!

Surrogate keys are very widely used in practice, much to the horror of purists: the rest of us just use them! See this Stack Overflow answer (as well as the rest of the thread).

  • I'll take less data duplication and an easier PK/NK update path over any notion of purity.
    – StingyJack
    Commented Aug 7, 2019 at 13:45
  • 1
    Well, ya gets nutting for nuttin in this life! If you have a surrogate key, then you can get duplication of the natural key data - one of the reasons purists don't like surrogates. If you a natural key but for reasons of [simpler joins | company policy | take your pick...] you want to use a surrogate key, be sure and have a NOT NULL specification for the field(s) and a UNIQUE index on that/those field(s)...
    – Vérace
    Commented Aug 9, 2019 at 14:01

I think we should start out clarifying that we are talking about Primary Keys, not alternate keys, right?. And since we are talking about Primary Keys, it is best that they do not change over time. And also best to be as small as feasible, especially if there will be any FKs pointing to this PK.

A major reason to avoid natural keys (outside of not having it in all cases yet — and 5 years is a long time to wait, especially for something that might not even happen) is the reliability factor: can you trust the source of the data? Is it machine generated or entered by a person? Humans entering data can, and often do, make mistakes. And even if the data is machine generated, it is still possible that a bug in the import process does something that mangles the values somehow. For this reason alone I tend to prefer surrogate keys. Sure, there are people who will complain that surrogate values are "meaningless", but we aren't talking about looking at a logical data model on paper or on screen. A value that is reliable and efficient will win over "meaningful" every time. A 30 byte (or whatever) key copied into several tables (and then into several more indexes) that will most likely be compared (even for JOINs) with a case-insensitive Collation makes no sense when one can choose a 4 byte INT that is a simple binary comparison.

Your "natural" key column should be indexed and is an alternate key. This is what the app will use to look up the record to get the surrogate key that will be used in all subsequent JOINs and queries. This column can change — datatype, length, values, etc — over time as much as the business requests, all without requiring you to tell the folks above you that the requested change will take an extra 1 - 6 months (depending on size of the system and scope of the change) because it affects so many tables, which means more time to plan, more time to develop the changes, more time needed for testing, and more risk that something won't be caught in testing and will cause 1 or more customers to log support requests which upsets customers and is a drag on support staff.

A composite key, while not inherently wrong, doesn't seem to add anything here. And in fact, it would seem to counter the uniqueness of the existing serial numbers for those device types that have them.

I would also not recommend using "the three unique attributes [to] create a hash in the program and use that as a surrogate key". Again, that seems like a value that can change if any of those three "unique attributes" ever change. It will also be larger than ideal for use as a PK, not to mention the non-sequential nature of a hash that will leave you in the same boat as those who started out thinking that GUIDs were a great choice for PKs ;-).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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