Natural key versus Surrogate key on a 1 column table

Question: I maintain a database table with 1 column. It is a lookup table for  object ids. The column is called “object_id.” The values in the table are unique. Should I leave the table with 1  column and use the values as the key or should I add a column with incremental ids.?

Answer:   Without being able to see the actual data , I would initially say both options can be valid in the right circumstances. But you metioned the values are unique and they are object ids. Based on  that information I’d opt for using the column “object_id” as the natural key.  It isn’t obvious that any extra value is added by adding a surrogate key.

Before deciding on which approach to use , review some of the arguments for and against using a natural key or surrogate key.

Natural key versus Surrogate key. A natural key  is composed of an attribute already in the real world. A surrogate key is a unique identifier for an object already in the database.   The main difference is that the surrogate key is not part of the application data , and would have no meaning outside of the database. For example:

Name_id             Surname

1. Santana
2. Hendrix
3. Satriani

Santana, Hendrix and Satriani all have meaning. 1,2,3  not associated with the Surname, are just a set of numbers.

Using a natural key has its advantages and  disadvantages

1. The user can derive meaning by the key itself. For example a National Insurance number
2. Depending on how they are used , the value can be difficult to age
3. Potentially (although not always) less JOINS may be needed

Surrogate keys

1. No meaning to the user if presented as a singular value
2. The main advantage is they don’t change , which mean any value associated can be edited
3. Debatable : but may require different approach to queries , such as additional JOINS

If you'd like to read more on Primary keys and arguments for\against on Auto generated Primary keys read How to decide on the Primary Key - SQL Server DBA

Read More on data modelling and data architecture

DBA - Database Architect - Data Architect - What’s the difference ?

DBA Ignorance , complacency and arrogance experiment