RDBMS

Something that only Surrogate Keys can do

There are two kinds (three with smart key, but the third one is considered worse) of primary keys, one is natural key and another is surrogate key. Simply said, surrogate key is an additional column with type of int (or guid), and unique, used as identity of row. While natural key is natural attribute that can distinctive the row. Both have pros and cons and I don’t want to discuss them here, I just want to share that there is one (two) things that can only be done by surrogate key.

First one, code generation

It does not means that natural key cannot have code generation tools. Natural key can consist of multiple columns (composite key) and different data type. The non-standard format makes code generation tools harder to develop. Meanwhile surrogate key will have standard and consistent format, which will make code generation easier.

Reference table and generic data operation

This is a case where I think can only be done by surrogate key (or at least, surrogate column). A reference table I defined here is something like “a table that hold reference to records from several tables”.

For example, say that you have a table called “submission trail”. That table is responsible to hold all transaction activity related to a record in all tables in all database. Let’s say that it has the following content:

database_name table_name reference_key action utc_time
db01 t001 1 Insert 2015-07-08T00:00:00

The reference key is referencing to primary key of table and act as identifier. Using natural key, especially composite key will prevent you from creating such table. The same reference table can also be used to other utilities things, for example: attachment or notification.

Generic data operations also need consistent column and data type keys to operate. What I means by generic data operations here is an operation that can be applied to any entities. Repository’s GetByKey is one of them. You can make a generic operation GetByKey, have specific implementation and returning the specified entity type. All of it cannot be done without consistent column.

Conclusion

In this case, We can see two patterns / techniques that cannot be used by natural / composite keys (or to be precise, require a constant key format). Both can be useful for utility operations and data-logging activities.