IDENTITY madness..

Mon Nov 24, 2003 by byrmol

What is wrong with this Schema?

CREATE TABLE IdentityTable (ParentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY)
GO
CREATE TABLE ChildOfIdentity(ChildID INT NOT NULL PRIMARY KEY, ParentID INT NOT NULL
CONSTRAINT FK_ForeignKey FOREIGN KEY (ParentID) REFERENCES IdentityTable(ParentID) ON UPDATE CASCADE)
GO

Legacy Comments


robvolk 2003-11-24	re: IDENTITY madness.. hehehehehehehehehehehehehehehehehehe, that's a good one David.

Clayton Firth 2003-11-24	re: IDENTITY madness.. I may have misunderstood the problem but... With T-SQL how do you insert a record into a table with a single column which is an identity? Short of enabling identity insert?

Clayton Firth 2003-11-24	re: IDENTITY madness.. Mmmm did miss the point slightly.. You're not responsible for this guf are you Dave?

DavidM 2003-11-24	re: IDENTITY madness.. Clayton, Use DEFAULT VALUES to insert.. In this case... INSERT IdentityTable DEFAULT VALUES INSERT ChildOfIdentity (ChildID, ParentID) VALUES (1,SCOPE_IDENTITY()) The main point is the UPDATE CASCADE clause.. Absolutely useless for an Identity column which cannot be updated!!!!

Clayton Firth 2003-11-24	re: IDENTITY madness.. Yeah got that about 2 seconds after i posted the first message.

Jeff 2003-11-24	re: IDENTITY madness.. Is this one of those "reasons to never ever use an identity column" points, or is there something else I'm missing? By definition, you use an identity because you don't care what the value of the column is and it has no meaning; thus, why would it ever need to change?

Tim 2003-11-25	re: IDENTITY madness.. I dont get why the childID is not an identity too ?

DavidM 2003-11-25	re: IDENTITY madness.. Jeff, You're not missing anything here. I disagree with your definition though. Regardless of the users point of view (they may never see it), it would be vital to the schema's correctness if that value is used as a FK. It is simply an example of the Identity property disrupting a schema's purpose. I was hoping SQL Server would be smart enough to tell me that I was stupid to do so. Practically the example is useless, in the same way that adding the Identity property to a Foreign Key would be useless. Tim, the ChildID is irrelevant to the point.

Jeff 2003-11-25	re: IDENTITY madness.. I see ... it would be nice if SQL would tell us when we are being stupid !! (But then, for me, it would probably never shut up!) :) Me: SELECT * FROM Table WHERE ID in (@CSV_Of_IDs) SQL: "is that really what you mean? this won't work with a CSV variable, you know!" Me: WHILE @i<10 BEGIN: SELECT @i : END SQL: "You know this will go on forever, don't you !?" Me: SELECT * FROM Tbl WHERE A=1 AND A=2 SQL: "I think you mean to use OR, not AND, you moron!" actually ... kind of fun to think of the warnings SQL could give you .... this might be a fun exercise !