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

> 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.

I realise by saying DIDN'T IT you actually haven't even run the syntax
yourself because you would find the CREATE TABLE itself fails.

Do you even have SQL SErver installed?

>
> 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

That's the whole point as you well know - it';s immutable so you can't do
that - follows Codd's rule....

> 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:

Follows Codd's rule....

Why do you need ordering? foo_id is used as a surrogate key.

Why do you not have a UNIQUE constraint on it and a PRIMARY KEY on bar_code?

> 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.

How many more times - IDENTITY is NOT NOT NOT NOT a flaming DATA TYPE!!!!!!!

It's a PROPERTY of a column.

Again, follows Codd's rule for immuatable.

> Let's add an explicit DEFAULT clause! Opps! That is not allowed and
> that is a basic column property.

How many more times - IDENTITY is NOT NOT NOT NOT a flaming DATA TYPE!!!!!!!

It's a PROPERTY of a column.

>
> CHECK() constraints seem to work with IDENTITY, but you get some weird
> stuff.

Like what? Example - you are making it up again. Myth mungering....

> 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

Yes, I read Books Online on a daily basis.

> 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 how many more times do you want to be the odd ONE out in the whole
industry?

The user is the application, the user is NOT the developer.

Using IDENTITY as a surrogate is fine for application plumbing so long as
the user in front of the application does not see or use 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.

You've just got a beef about IDENTITY because you totally misunderstand the
basics on it - go back and read the f'in manual.

--
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]

[Back to original message]