Re: does a table always need a PK?

Centuries ago, Nostradamus foresaw when Paul <paul_at_not.a.chance.ie> would write:
> cbbrowne_at_acm.org says...
>
>> You almost certainly should have a UNIQUE primary key on every relation.
>
> Maybe this true in theory, but not essential in practice?
>
> I'm not being facetious here.

Do you want counties to be unique?

If you do, then you need a UNIQUE key that will amount to providing a PK.

> Now, as I understand it, RDBMS's will look at the size of a table
> before scanning it to see if it's worthwhile using an index, and if
> the table is too small, it'll just perform a straigh scan anyway and
> not bother with the index, even if you've gone to the trouble of
> putting one in.

Size is irrelevant to the matter.

The issue is of whether or not you want to use a relational database to actually maintain some aspects of correctness.

If you don't care about correctness, then feel free to do whatever you like.

I have seen database systems that accept invalid inputs, silently transforming erroneous data into other erroneous data. (Indeed, that is true for date handling for one of the most popular "open source" database systems.) That doesn't give me any "warm fuzzies" about the wisdom of using it to store data of any importance.

Having the UNIQUE index is a way of making sure that the data in the database is CORRECT (or at least of preventing /some/ errors); I don't give a rip whether the index is used for queries; the point of the exercise is to keep from filling the database with garbage.

> AFAIK, this is true for the RDBMS's that I use (Interbase, FireBird
> and PostgreSQL).

You seem to be falling into the trap of becoming dependent on the physical and/or logical representation of the data. Codd's principles include that activities should remain unimpaired when changes are made to physical and logical storage representations or access methods.

I could not care less whether or not the "region" index gets used to access the data. The point of the index, in this case, is NOT to improve access times, but rather to enforce uniqueness of the primary key. What the query planner does or does not do is totally irrelevant to that point.

-- 
let name="cbbrowne" and tld="ntlug.org" in name ^ "_at_" ^ tld;;
http://www3.sympatico.ca/cbbrowne/spreadsheets.html
"I'm sorry, the teleportation booth you have reached is not in service
at this  time.   Please  hand-reassemble  your molecules or   call  an
operator to help you...."