Re: Identity key?

Joe Celko wrote:
>
> >> I have a question regarding an observation made by Joe Celko in an
> article... The correct declaration should have been something like this:
>
> CREATE TABLE US_States
> (state_code CHAR(2) NOT NULL PRIMARY KEY,
> state_name VARCHAR(15) NOT NULL UNIQUE);
> ======================================================================
>
> Wouldn't be better to have the table like this?
>
> CREATE TABLE US_States
> (state_id IDENTITY,
> state_code CHAR(2) NOT NULL PRIMARY KEY,
> state_name VARCHAR(15) NOT NULL UNIQUE); <<
>
> No. What the hell does state_id mean in terms of a LOGICAL model?

I tend to disagree. The relational model is not really suited for logical modelling anyway; that's why entity-relationship modelling was invented. But if you are using it as such then the term PRIMARY KEY is nonsense, because that describes an implementation aspect. So if your position is that surrogate keys have no place in a logical model, then primary keys should also be avoided.

-- 
  Jan Hidders