Re: SQL Express - Identity specification property - how to change

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]

.

Relevant Pages

  • Re: Newbie help
    ... > reason I decided to store the data in a SQL Server DB. ... Then as the day goes on all tick data is simply inserted into ... >> with the most recent (as compared to the time parameter) non-null values ... Go back to basics. ...
    (microsoft.public.sqlserver.programming)
  • Re: Newbie help
    ... I quoted the word real-time because in this case real-time means 1 tick ... the data in a SQL Server DB. ... Go back to basics. ... > do not work at that level, if you want to have any data integrity. ...
    (microsoft.public.sqlserver.programming)
  • Re: How to build a procedure that returns different numbers of columns as a result based on a parame
    ... maintain and violates some of basic ideas of RDBMS. ... SQL is not an application language; it is a data retrieval language. ... to cram everything into one SQL module. ... DeMarco, Myers, etc. and the basics of structured programming. ...
    (comp.databases.ms-sqlserver)
  • Re: MCDA(SQL 2005)
    ... This will provide you with basic understanding of ANSI SQL92 commands common to all databases using SQL ie the offical standard. ... Every database has its own extentsions to SQL that are specific to only its product. ... So make sure you stick to sites that cover strick ANSI92 commands for the basics and then look only at those that cover T-SQL to get the full coverage of SQL Server brand of SQL. ... >> exams 70-443 and 70-444 to become DBA. ...
    (microsoft.public.cert.mcdba)