<< Using UDF's for n Cardinality relationships. | Home | Strange RI >>

IDENTITY madness..

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

Print | posted on Monday, November 24, 2003 10:35 AM

Feedback

# re: IDENTITY madness..

left by robvolk at 11/24/2003 12:05 PM Gravatar
hehehehehehehehehehehehehehehehehehe, that's a good one David.

# re: IDENTITY madness..

left by Clayton Firth at 11/24/2003 2:08 PM Gravatar
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?

# re: IDENTITY madness..

left by Clayton Firth at 11/24/2003 2:16 PM Gravatar
Mmmm did miss the point slightly.. You're not responsible for this guf are you Dave?

# re: IDENTITY madness..

left by DavidM at 11/24/2003 2:40 PM Gravatar
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!!!!

# re: IDENTITY madness..

left by Clayton Firth at 11/24/2003 3:45 PM Gravatar
Yeah got that about 2 seconds after i posted the first message.

# re: IDENTITY madness..

left by Jeff at 11/24/2003 11:43 PM Gravatar
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?

# re: IDENTITY madness..

left by Tim at 11/25/2003 12:57 AM Gravatar
I dont get why the childID is not an identity too ?

# re: IDENTITY madness..

left by DavidM at 11/25/2003 8:09 AM Gravatar
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.

# re: IDENTITY madness..

left by Jeff at 11/25/2003 9:54 AM Gravatar
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 !
Comments have been closed on this topic.
Copyright © byrmol