Posted by --CELKO-- on 11/26/07 23:03

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