Re: Identity key?
From: DODO <dodo_at_Ihatespam.com>
Date: Mon, 8 Jan 2001 10:32:12 -0500
Message-ID: <14l66.115465$Z2.1362163_at_nnrp1.uunet.ca>
>
> >> 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? It
> would just be the random order that the rows were PHYSICALLY inserted
> into this table. Both state_code and state_name are UNIQUE, but you
> need to pick one to be the PRIMARY KEY. Since state_code is the
> shortest one and the one mostly likely to be used, I made it the
> PRIMARY KEY.
>
> PRIMARY KEY and NOT NULL UNIQUE are not quite equivalent in SQL. Both
> define a column or set of columns as a key, but the PRIMARY KEY is the
> default of a REFERENCES clause. And some products use special indexing
> methods for the PRIMARY KEY.
>
> --CELKO--
> Joe Celko, SQL Guru & DBA at Trilogy
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> which can be cut and pasted into Query Analyzer is appreciated.
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Mon Jan 08 2001 - 16:32:12 CET
Date: Mon, 8 Jan 2001 10:32:12 -0500
Message-ID: <14l66.115465$Z2.1362163_at_nnrp1.uunet.ca>
Mr Celko,
Thanks for answering.
I was viewing state_id as a surrogate key. I've seen many schemas that use
them. And these(now I realised) were the real questions:
- Do they(the surrogates) belong to the logical model?(From your answer I infer that NO)
- A standard which has a surrogate key for every table, declared Primary Key and the "real" key declared as Alternate key is bad?(again from your answer I infer that YES). TIA, DODO
>
> >> 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? It
> would just be the random order that the rows were PHYSICALLY inserted
> into this table. Both state_code and state_name are UNIQUE, but you
> need to pick one to be the PRIMARY KEY. Since state_code is the
> shortest one and the one mostly likely to be used, I made it the
> PRIMARY KEY.
>
> PRIMARY KEY and NOT NULL UNIQUE are not quite equivalent in SQL. Both
> define a column or set of columns as a key, but the PRIMARY KEY is the
> default of a REFERENCES clause. And some products use special indexing
> methods for the PRIMARY KEY.
>
> --CELKO--
> Joe Celko, SQL Guru & DBA at Trilogy
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> which can be cut and pasted into Query Analyzer is appreciated.
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Mon Jan 08 2001 - 16:32:12 CET