Post reply

Temp Table Foreign Keys

  • August 1, 2016 at 10:59 pm

    #320322

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

  • August 1, 2016 at 11:01 pm

    #1893106

    Good question.

  • August 2, 2016 at 12:06 am

    #1893109

    Nice spot, t.a.

    Iulian

  • August 2, 2016 at 12:25 am

    #1893113

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

  • August 2, 2016 at 12:57 am

    #1893118

    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 http://www.sqlservercentral.com/articles/Best+Practices/61537

  • August 2, 2016 at 1:14 am

    #1893126

    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

  • August 2, 2016 at 2:30 am

    #1893147

    Thanks for the question. Interesting one.

    Igor Micev,My blog: www.igormicev.com

  • August 2, 2016 at 2:30 am

    #1893148

    It looks OK when testing if the values are inserted:

    ...

    SELECT * FROM #TableTest1

    SELECT * FROM #TableTest2

  • August 2, 2016 at 2:57 am

    #1893156

    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.

  • August 2, 2016 at 4:57 am

    #1893172

    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

  • August 2, 2016 at 5:07 am

    #1893173

    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

  • August 2, 2016 at 7:09 am

    #1893215

    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.

  • August 2, 2016 at 7:57 am

    #1893227

    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.

  • August 2, 2016 at 8:01 am

    #1893230

    Nice question, Thanks for sharing.

  • August 2, 2016 at 9:35 am

    #1893271

    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 19 total)

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