Posted by Tony Rogerson on 11/27/07 05:49

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

Just get them to do this....

> CREATE TABLE FOO
> (
> FOO_ID int identity(1,1),
Server_name varchar(20) ----> UNIQUE <-----
> )

Then all is well, well it would have been if they are started out properly
in the first place.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"Greg D. Moore (Strider)" <mooregr_deleteth1s@greenms.com> wrote in message
news:13kn4214hevva58@corp.supernews.com...
>
>
> 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]