Temp Table Foreign Keys

  • David Fundakowski

    Ten Centuries

    Points: 1290

    Comments posted to this topic are about the item Temp Table Foreign Keys

  • Terje Hermanseter

    Hall of Fame

    Points: 3757

    Good question.

  • Iulian -207023

    SSCertifiable

    Points: 7507

    Nice spot, t.a.

    Iulian

  • BillLudlow

    SSCertifiable

    Points: 6160

    I got the question right, but I thought the correct answered should have been qualified a bit more.

  • Neil Burton

    SSC-Insane

    Points: 21900

    Define successfully; if there's a warning generated and something, that could be critical, is not done, is that successful? The right answer is right but I don't think it's complete.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help [/url]

  • BrainDonor

    SSCoach

    Points: 19191

    I agree - the correct answer choice should be expanded upon. It isn't a syntax error, but it doesn't complete the full statement either.

    Steve Hall
    Linkedin
    Blog Site

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Thanks for the question. Interesting one.

    Igor Micev,
    My blog: www.igormicev.com

  • Iulian -207023

    SSCertifiable

    Points: 7507

    It looks OK when testing if the values are inserted:

    ...

    SELECT * FROM #TableTest1

    SELECT * FROM #TableTest2

  • paul.knibbs

    SSCoach

    Points: 15270

    Got it wrong because I didn't realise SQL didn't enforce foreign keys on temp tables, so I learned something. Bit more information would have been nice, though, as said above.

  • TomThomson

    SSC Guru

    Points: 104762

    Nice question.

    It indicates yet another piece of really sloppy human interface design on Microsoft's part. if Foreign Keys on temprorary tables are not enforced, allowing them to be declared without raising at least a warning is absolutely appalling user interface design, and ideally an errorshould be raised and the statement containing the constraint declaration failed.

    It's a mitigating factor that a warning is raised when a DML operation that would fail if the table involved were permanent suceeds because the table is temporary, but that's only a small mitigation.

    edit: I never declare foreign keys on temporary tables.

    Tom

  • Iulian -207023

    SSCertifiable

    Points: 7507

    TomThomson (8/2/2016)


    Nice question.

    It indicates yet another piece of really sloppy human interface design on Microsoft's part. if Foreign Keys on temprorary tables are not enforced, allowing them to be declared without raising at least a warning is absolutely appalling user interface design, and ideally an errorshould be raised and the statement containing the constraint declaration failed.

    It's a mitigating factor that a warning is raised when a DML operation that would fail if the table involved were permanent suceeds because the table is temporary, but that's only a small mitigation.

    edit: I never declare foreign keys on temporary tables.

    It raises an warning to me:

    Skipping FOREIGN KEY constraint 'FK_TableTest1_ID' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

    But good point, why would one use Foreign Keys in temporary tables ?

    Anyway the question is interesting

    Cheers,

    Iulian

  • x

    SSC-Insane

    Points: 23352

    TomThomson (8/2/2016)


    Nice question.

    It indicates yet another piece of really sloppy human interface design on Microsoft's part. if Foreign Keys on temprorary tables are not enforced, allowing them to be declared without raising at least a warning is absolutely appalling user interface design, and ideally an errorshould be raised and the statement containing the constraint declaration failed.

    It's a mitigating factor that a warning is raised when a DML operation that would fail if the table involved were permanent suceeds because the table is temporary, but that's only a small mitigation.

    edit: I never declare foreign keys on temporary tables.

    I really agree with this. I think its an out and out bug. You're giving an instruction that IS NOT A NO-OP to a computer programming system and it is IGNORED.

  • George Vobr

    SSCrazy Eights

    Points: 8931

    Thanks David for an interesting question.:-)

    Skipping FOREIGN KEY constraint at temporary tables in this script prevent the error:

    "Msg 547, Level 16, State 0, Line 19

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TableTest1_ID..."

    which would occur at a common table.

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question, Thanks for sharing.

  • sestell1

    SSChampion

    Points: 10230

    Iulian -207023 (8/2/2016)


    TomThomson (8/2/2016)


    Nice question.

    It indicates yet another piece of really sloppy human interface design on Microsoft's part. if Foreign Keys on temprorary tables are not enforced, allowing them to be declared without raising at least a warning is absolutely appalling user interface design, and ideally an errorshould be raised and the statement containing the constraint declaration failed.

    It's a mitigating factor that a warning is raised when a DML operation that would fail if the table involved were permanent suceeds because the table is temporary, but that's only a small mitigation.

    edit: I never declare foreign keys on temporary tables.

    It raises an warning to me:

    Skipping FOREIGN KEY constraint 'FK_TableTest1_ID' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

    But good point, why would one use Foreign Keys in temporary tables ?

    Anyway the question is interesting

    Cheers,

    Iulian

    That's not a warning, in that no warning code is raised. It's just an informational message letting you know that the foreign key was not created.

Viewing 15 posts - 1 through 15 (of 20 total)

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