Re: Identity key?

>> 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 Sun Jan 07 2001 - 15:49:12 CST