The occasion for this post is a little contretemps I experienced on StackOverflow.com recently. The topic of this blog post is the question posed by acidZombie24, a member of StackOverflow, over 3 years ago. I was one of those who responded to the question. My answer was:

Databases don’t have keys, per se, but their constituent tables might. I assume you mean that, but just in case…

Anyway, tables with a large number of rows should absolutely have primary keys; tables with only a few rows don’t need them, necessarily, though they don’t hurt. It depends upon the usage and the size of the table. Purists will put primary keys in every table. This is not wrong; and neither is omitting PKs in small tables.

Note that the statement “Database don’t have keys…” refers to the original text of the question title, which was subsequently changed by an edit.

This answer did not make much of an impression on anyone (the questioner never marked any of the answers as Accepted and nobody gave me any upvotes), until just the other day. And this was a downvote (which subtracts reputation points). If warranted, I don’t mind a downvote, if it is a legitimate beef with my answer, but given the downvoter’s comment I thought it unwarranted. He commented on his downvote, and a little conversation ensued:

jmoreno: A single row table doesn’t need a primary key, anything else should have one defined to avoid duplicates. – Jun 21 at 0:57

Cyberherbalist: Yes, generally, but business rules determine whether duplicates are to be permitted — it is not inconceivable that duplicate entries in a table might not only be permissible, but expected. It depends upon what is being stored, and what use is made of it. BTW thanks for the rep hit — this answer doesn’t actually contradict the accepted answer. Your absolutism is noted. – Jun 21 at 17:15

jmoreno: If you’re storing exact duplicates, you’re storing the wrong thing. As for the rep hit, remove the slam at people that think that every table should have a PK, and I’ll remove it. – Jun 21 at 18:28

I was a little puzzled about the reference to my supposed “slam” at people who think that every table should have a PK. I looked over my answer and comments to others’ answers for any insults and did not see any. Unless by “slam” he meant the term “purist”? Perhaps he thought this was intended as an insult? It wasn’t so intended — heck, I am a purist about certain things, and I think I’m justified in those cases, and accept that differences of opinion are natural consequences of free speech. In fact, I am a purist when it comes to people trying to bully me around, and thus I will not remove the “slam.” I will simply wear the loss of 2 reputation points as a badge of obstinacy! No problemo.

It just so happens that at the moment I am working on a little utility at work which accesses a table that has no Primary Key. And that was not due to oversight by our typically conscientious and highly competent Data Administration staff. In this case, the table stores rows which, once inserted, are never updated or deleted. Our DA staff are really in love with “natural keys” (sometimes to a fault) as Primary Keys, and if they had thought a PK was necessary, then By Golly that’s we would have gotten, LOL. But this is the table:


CREATE TABLE [dbo].[agency_message](
[agency] [char](3) NOT NULL,
[subagency] [char](1) NOT NULL,
[effective_date] [smalldatetime] NOT NULL,
[message_text] [varchar](1000) NOT NULL,
[requested_by_user_name] [varchar](50) NOT NULL
)


The table is used as follows: when a user signs into the application, the system compares the system date/time with the “effective_date” in the table, and uses the row with the largest effective_date whose effective_date is not greater than the current system date/time, if one exists (the additional criterium is a match on agency/subagency).

The reason there is no primary key is because no row is ever updated or deleted once inserted, and we retain all rows for the sake of having a history of agency messages.

Given that our DA staff are, with all due respect and good will, purists of the best stripe, I consider that this table’s lacking of a primary key to be arguable evidence that tables do not always require primary keys. Business rules, as I said above, must prevail, and in this case, the Business Rules dictated the table structure.