On Mon, Jun 28, 2010 at 02:15:01PM -0400, Tim Romano scratched on the wall:
> Since no SQL standard requires the primary key to do anything other than be > unique within the relation and with respect to its foreign references. As > long as the database maintains meets those requirements, it is free to > change the PK value as an "implementation detail" -- provided RI is not > broken in the process. The purist in me says the PK is for nothing but > uniqueness. It should have no other meaning whatsoever. That is definitely not true in the SQL world or the Relational Model. Not in the general case, anyways. One must assume that a PK (in SQL) or a candidate key (RM) contains real data that consist of meaningful values. For example, a store register database might use UPC for a PK value. The concept of a primary key (or more specifically, a candidate key) is an attribute that is applied to existing columns, not the other way around. This becomes even more apparent when you consider that the Relational Model does not have PKs, but only candidate keys-- and, unlike SQL PKs, a relation (table) is allowed to have more than one candidate key. They can't all be arbitrary. Now, it is true that many database designs choose to omit a meaningful PK and use a so-called "surrogate key." This is typically an arbitrary numeric ID field, but it could be anything. Surrogate keys are commonly used when there isn't a very strong natural PK (such as with people) or when the natural PK is clumsy or large (say, several long text columns). It is also true that this technique is so common and so popular that many databases (including SQLite) support some type of auto-assignment system (such as INTEGER PRIMARY KEY). But it is equally valid to define a text field or a manually assigned integer field as the PK of a table. Just because a PK can be arbitrary doesn't mean it must be. While you might argue that the database engine can do as it pleases with surrogate keys, that is most definitely not true of PKs in general, and I think there are strong arguments to say that it would be a Bad Idea to do it with surrogate keys. For one thing, you'd have to very strictly notify the database when it was dealing with a surrogate key. Simply having something be an INTEGER PRIMARY KEY would not be good enough, for example. I can still define a PK as such and then enter my own meaningful numbers. If you speak of internal cross references, such as ROWIDs, that's a whole different story. General ROWIDs, OIDs, or whatever the database wants to call them, are typically the domain of the database engine, and not to be used or exposed. They're still used as keys (for example, index record references) and I would quickly agree that they are open to changes and modifications whenever and however the database system wants. SQLite does this with a VACUUM, for example. ROWID values can change, but PK values cannot be changed or altered. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
