Posted by Greg D. Moore \(Strider\) on 11/27/07 03:36

BTW, all this talk just reminded me of a problem at a client site.

They are making unfortunately frequent use of the IDENTITY column in
conjunction with Primary Key.

So... we have something like

CREATE TABLE FOO
(
FOO_ID int identity(1,1),
Server_name varchar(20)
)

And then the PK is set to FOO_ID

Then a script that says:
insert into FOO Values ('server_bob');
insert into FOO Values ('server_chris');

Unfortunately, if this script is rerun, we now have in the table

1, server_bob
2, server_chris
3, server_bob
4, server_chris

So.. now we have some queries that end up seeing 1 OR 3 for server_bob and 2
or 4 for Server_chris (FOO_ID is a foreign key into other tables whose
inserts I've left out).

So, you can't do a join on FOO correctly. Very nasty.


I've brought this up a few times and still waiting for them to fix it.

Now, personally I'm a fan of the Identity column... it can be useful at
times.... this is certainly NOT one of them.


--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:653a8c55-6b2c-4ee9-8143-e1bc65682ac4@s36g2000prg.googlegroups.com...
>> 1) NULL-able
>> 2) More than one column can have the same data type
>> 3) Has to take CHECK() constraints
>> 4) Appropriate computations can done on it (numeric, string or
>> temporal)
>> IDENTITY has none of the properties of a data type because it is not a
>> data type at all.
>
>>> IDENTITY is a property that we give to one column in the table in the
>>> same vein that we can only give the PRIMARY KEY property to one column
>>> in the table. <<
>
> Yes, like the PRIMARY KEY property, it is a property of the *table* as
> a whole. It is not a data type, which was my point. It is based on
> the order of physical insertion into physical storage in a particular
> table of a particular schema on a particular piece of hardware in a
> particular release of SQL Server. It has nothing to do with a data
> model.
>
> Dr. Codd admitted the PRIMARY KEY property was a mistake shortly after
> his first papers. Unfortunately, it got into SQL because we were
> basing the first SQL products on a sort key used for tape merging!
>
>>> The column that has the IDENTITY property can have all the aspects you
>>> speak of - you are completely wrong. <<
>
> Try this:
>
> CREATE TABLE Foobar
> (foo_id INTEGER IDENTITY (1,1) NULL,
> bar_code INTEGER NOT NULL);
>
> INSERT INTO Foobar (42);
>
> It failed, didn't it? It is not NULL-able! This is a Basic
> requirement of a data type.
>
> INSERT INTO Foobar (foo_id, bar_code) VALUES (-12, 4);
>
> It failed, didn't it? It does not allow insertion of a value; another
> basic data type property. Another one is
>
> INSERT INTO Foobar
> SELECT new_bar FROM Floob;
>
> What is the ordering of the foo_id values? Unpredictable! But since
> it is exposed to the user, it should be deterministic. This is a
> version of the Information Principle, but you probably don't see it.
> Now try this one:
>
> UPDATE Foobar SET foo_id = foo_id -2;
>
> It failed, didn't it? you are not allow to update IDENTITY; another
> basic data type property. They do DELETE okay, however.
>
> Let's add an explicit DEFAULT clause! Opps! That is not allowed and
> that is a basic column property.
>
> CHECK() constraints seem to work with IDENTITY, but you get some weird
> stuff.
>
> Since you don't read Standards, UNIQUE and PRIMARY KEY constraints are
> called uniqueness constraints in SQL. I can have UNIQUE on a NULL-
> able column. The PRIMARY KEY is defined as UNIQUE + NOT NULL, with a
> restriction that it is used only once. This makes the column(s) the
> default targets of REFERENCES clauses and many older SQL products do
> special things with it to speed up searching. There have been
> proposals that we drop it in modern RDBMS, but the "code museum
> effect" is very strong
>
>>> People use IDENTITY successfully for surrogate keys and they follow all
>>> Codd's rules for surrogates - but the DB world has moved on from Codd's
>>> original definitions - see Date and others for a start. <<
>
> No, they don't; how many times do I have to post the quote about being
> exposed to a user? That means you can see it, you had to explicitly
> declare it and you can do operations on it.
>
> And you might actually want to read some of Date's papers. While we
> disagree on much, we are both "big fans" of Dr. Codd and natural keys.
> He is probably more so than me -- I don't mind artificial keys if you
> can control them properly.
>

[Back to original message]