4

While I've been working with querying SQL databases for a while, I'm still quite the novice at actually building good tables. One thing I often struggle is with primary keys.

In a table I'm creating now for logging errors on some alarm equipment, I need 5 columns. The park and code columns uniquely identifies a site (although the park column is atm. not used). Then there is a serial column identifying the equipment in question, and an error containing the error code. Lastly there is a timestamp for when an error is logged.

Each site have several different pieces of equipment, and an piece of equipment may report several errors. When an error is corrected, the row is removed from the table.

Thus to uniquely identify an error, we need to check park, code, serial and fault. All of these seems like good candidates for indexes based on the queries likely to be run. However it doesn't seem right to me to define all of these as an combined primary key, they are after all almost all the columns in the table!

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

Jo-Herman Haugholt
  • 462
  • 1
  • 5
  • 15
  • What RDBMS are you using? (There might be physical considerations worth mentioning as well that are dependant on this) – Martin Smith Nov 10 '11 at 12:11
  • For the case in the example, I'm suffering through SQL Server 2000, although I've struggled with similar problems on MySQL and PostgreSQL. – Jo-Herman Haugholt Nov 10 '11 at 12:14
  • You face two options: natural composite key or surrogate key with composite unique constraint. Problem is, there doesn't seem to be definite solution. DB purists will advocate PK (no extra "artifical" column), those who are used to frequent changes in their model will prefer SK+U... – MaR Nov 10 '11 at 13:08

1 Answers1

3

A lot of people will just tell you to add an id number to that table, and make it the primary key.

I'm not one of those people.

If the columns {park, code, serial, fault} are necessary to uniquely identify a row, and thereby to prevent duplicates, then the dbms needs to know that. You inform the dbms of that requirement in one of two ways.

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

In some circumstances, it makes sense to use a smaller surrogate key (an id number) as the primary key. That doesn't relieve you of the responsibility for informing the dbms of the true state of affairs. (With NOT NULL UNIQUE (park, code, serial, fault).) I don't think this applies to your situation, though.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185