FK to IDENTITY

Question

FK to IDENTITY

April 25, 2013 at 2:01 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Dana Medley SSCertifiable Points: 6764 More actions · Answer 1

Great question and very well written. I kind of figured the IDENTITY_INSERT was more for ease of confusion reasons than anything rather than assuming it was off.

Everything is awesome!

mtassin SSC-Insane Points: 23099 More actions · Answer 2

Hugo Kornelis (4/24/2013)
Very good, and very clever question. Well done! 😉

I will totally second this statement. I sat there staring at the question...

I could rule out row one because it references row 2, which didn't exist yet

Row 2... referenced row 1... which the identity was used up due to the failed insert, but the failed insert meant that it too would fail

Finally row 3... was sneaky... ultimately I "went with it" and decided that it would insert... In all of what I've done, I've never tried to insert into a self referencing table a row that references itself, but I could see where that might be necessary (making the CEO report to himself in the company hierarchy instead of setting the identity of the employee he reports to, to null for example).

Thanks for making my brain have to work this morning!

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

nenad-zivkovic Default port Points: 1448 More actions · Answer 3

Thank you all for your comments. I am glad to hear you liked the question, as this is first time I am posting one. Idea for this came up after a similar question couple of weeks ago, so thanks to that poster as well.

_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

Rose Bud Hall of Fame Points: 3147 More actions · Answer 4

nenad-zivkovic (4/24/2013)
Thank you all for your comments. I am glad to hear you liked the question, as this is first time I am posting one. Idea for this came up after a similar question couple of weeks ago, so thanks to that poster as well.

Superb question. Please consider submitting more.

srienstr SSCrazy Points: 2435 More actions · Answer 5

I too got it right for the wrong reason, thinking the second insert was the one that succeeded.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 6

Excellent question. Drives home the point that, even if an insert fails on a table with an identity column, the identity value is 'consumed'.

Well done.

Rob Schripsema
Propack, Inc.

Miles Neale SSChampion Points: 13147 More actions · Answer 7

Rob Schripsema (4/24/2013)
Excellent question. Drives home the point that, even if an insert fails on a table with an identity column, the identity value is 'consumed'.
Well done.

Agreed! +1

Not all gray hairs are Dinosaurs!

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Really excellent question with a good clear explanation. More like thios onme, please!

Tom

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events