Choosing a good primary key for a table with multiple columns that are collectively unique

Question

While I worked with SQL database queries for some time, I am still new to creating good tables. One thing that I often encounter is primary keys.

In the table that I am creating now to register errors on some signaling equipment, I need 5 columns. The park and code tables uniquely identify the site (although the park column is not used). Then there is a serial column identifying the equipment in question and an error containing an error code. Finally, there is a timestamp when an error is logged.

Each site has several different pieces of equipment, and some of the equipment may report several errors. When the error is fixed, the row is deleted from the table.

Thus, in order to uniquely identify the error, we need to check park , code , serial and fault . They all look like good candidates for indexes based on queries that can be run. However, it seems to me impractical to define them all as a combined primary key, because they are almost all the columns in the table!

I struggled with similar problems several times, and I never felt that I had found a good solution. Anyone can suggest some good practices for tables like this, where most (or even all) columns are needed to uniquely identify a row?

Answer 1

Many people will simply tell you to add the identifier number to this table and make it primary.

I'm not one of those people.

If the columns {park, code, serial, fault} are needed to uniquely identify a row and thus prevent duplication, then dbms should know that. You are reporting dbms of this requirement in one of two ways.

• PRIMARY KEY (park, code, serial number, error)
• NOT NULL UNIQUE (park, code, serial number, error)

In some cases, it makes sense to use a smaller surrogate key (identifier number) as the primary key. This does not relieve you of the responsibility for reporting the true state of affairs. (With NOT NULL UNIQUE (park, code, serial, fault) .) I do not think this applies to your situation.