Temp Table Foreign Keys

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

  • Good question.

  • Nice spot, t.a.

    Iulian

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

  • 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

  • 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

  • Thanks for the question. Interesting one.

    Igor Micev,
    My blog: www.igormicev.com

  • It looks OK when testing if the values are inserted:

    ...

    SELECT * FROM #TableTest1

    SELECT * FROM #TableTest2

  • 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.

  • 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

  • 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

  • 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.

  • 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.

  • Nice question, Thanks for sharing.

  • 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