Primary and Foriegn Keys on Temp Tables

  • Raghavender Chavva

    SSCrazy Eights

    Points: 8669

    Comments posted to this topic are about the item Primary and Foriegn Keys on Temp Tables

    Thank You.

    Regards,
    Raghavender Chavva

  • This was removed by the editor as SPAM

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Nice Question

    Thanks

  • ako58

    Hall of Fame

    Points: 3385

    http://www.sqlservercentral.com/questions/Foreign+Keys+(FK)/144670/

    CREATE TABLE #TableTest1 (

    ID INT NOT NULL,

    CONSTRAINT PK_ID1 PRIMARY KEY(ID)

    );

    CREATE TABLE TableTest2 (

    ID INT NOT NULL

    CONSTRAINT FK_TableTest1_ID FOREIGN KEY (ID) REFERENCES #TableTest1(ID)

    );

    INSERT INTO #TableTest1 (ID)

    VALUES

    (1);

    INSERT INTO TableTest2 (ID)

    VALUES

    (2);

    select * from #TableTest1

    select * from TableTest2

    Correct answer:

    The query will complete successfully

    Explanation:

    The query will complete successfully and insert a row into both tables regardless of the foreign key constraint. SQL Server does not enforce foreign key constraints on temporary tables.

    https://msdn.microsoft.com/en-us/library/ms189049.aspx

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    ako58 (8/16/2016)


    http://www.sqlservercentral.com/questions/Foreign+Keys+(FK)/144670/

    CREATE TABLE #TableTest1 (

    ID INT NOT NULL,

    CONSTRAINT PK_ID1 PRIMARY KEY(ID)

    );

    CREATE TABLE TableTest2 (

    ID INT NOT NULL

    CONSTRAINT FK_TableTest1_ID FOREIGN KEY (ID) REFERENCES #TableTest1(ID)

    );

    INSERT INTO #TableTest1 (ID)

    VALUES

    (1);

    INSERT INTO TableTest2 (ID)

    VALUES

    (2);

    select * from #TableTest1

    select * from TableTest2

    Correct answer:

    The query will complete successfully

    Explanation:

    The query will complete successfully and insert a row into both tables regardless of the foreign key constraint. SQL Server does not enforce foreign key constraints on temporary tables.

    https://msdn.microsoft.com/en-us/library/ms189049.aspx%5B/quote%5D

    I got the following errors:

    Msg 1766, Level 16, State 0, Line 6

    Foreign key references to temporary tables are not supported. Foreign key 'FK_TableTest1_ID'.

    Msg 1750, Level 16, State 0, Line 6

    Could not create constraint. See previous errors.

    Neither table was created.

  • This was removed by the editor as SPAM

  • George Vobr

    SSCrazy Eights

    Points: 9197

    ako58 (8/16/2016)


    http://www.sqlservercentral.com/questions/Foreign+Keys+(FK)/144670/

    CREATE TABLE #TableTest1 (

    ID INT NOT NULL,

    CONSTRAINT PK_ID1 PRIMARY KEY(ID)

    );

    CREATE TABLE TableTest2 (

    ID INT NOT NULL

    CONSTRAINT FK_TableTest1_ID FOREIGN KEY (ID) REFERENCES #TableTest1(ID)

    );

    INSERT INTO #TableTest1 (ID)

    VALUES

    (1);

    INSERT INTO TableTest2 (ID)

    VALUES

    (2);

    select * from #TableTest1

    select * from TableTest2

    Correct answer:

    The query will complete successfully

    Explanation:

    The query will complete successfully and insert a row into both tables regardless of the foreign key constraint. SQL Server does not enforce foreign key constraints on temporary tables.

    https://msdn.microsoft.com/en-us/library/ms189049.aspx%5B/quote%5D

    In the QotD of 2016/08/02 were the both tables listed as temporary #TableTest1 and #TableTest2, it can be easy to overlook. :unsure:

  • Ed Wagner

    SSC Guru

    Points: 286982

    Nice question. It illustrates a good point. Thanks.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Interesting. I've never tried this but it's good to know. Thanks.

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    George Vobr (8/16/2016)


    ako58 (8/16/2016)


    http://www.sqlservercentral.com/questions/Foreign+Keys+(FK)/144670/

    CREATE TABLE #TableTest1 (

    ID INT NOT NULL,

    CONSTRAINT PK_ID1 PRIMARY KEY(ID)

    );

    CREATE TABLE TableTest2 (

    ID INT NOT NULL

    CONSTRAINT FK_TableTest1_ID FOREIGN KEY (ID) REFERENCES #TableTest1(ID)

    );

    INSERT INTO #TableTest1 (ID)

    VALUES

    (1);

    INSERT INTO TableTest2 (ID)

    VALUES

    (2);

    select * from #TableTest1

    select * from TableTest2

    Correct answer:

    The query will complete successfully

    Explanation:

    The query will complete successfully and insert a row into both tables regardless of the foreign key constraint. SQL Server does not enforce foreign key constraints on temporary tables.

    https://msdn.microsoft.com/en-us/library/ms189049.aspx%5B/quote%5D

    In the QotD of 2016/08/02 were the both tables listed as temporary #TableTest1 and #TableTest2, it can be easy to overlook. :unsure:

    That almost tripped me up, too.

    It wasn't until I tested the code that I realized TableTest2 was not a temp table.

    Nice question!

  • David Fundakowski

    Ten Centuries

    Points: 1310

    I thought this was an exact copy of my question from 8/2! Didn't catch table2 was not temp. Need coffee....

  • chgn01

    Hall of Fame

    Points: 3682

    CREATE TABLE TableTest1 (

    ID INT NOT NULL,

    CONSTRAINT PK_ID1 PRIMARY KEY(ID)

    );

    CREATE TABLE #TableTest2 (

    ID INT NOT NULL

    CONSTRAINT FK_TableTest1_ID FOREIGN KEY (ID) REFERENCES TableTest1(ID)

    );

    INSERT INTO TableTest1 (ID)

    VALUES

    (1);

    INSERT INTO #TableTest2 (ID)

    VALUES

    (2);

    select * from TableTest1

    select * from #TableTest2

    ----------

    this will be different.

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • Ken Wymore

    SSCoach

    Points: 16588

    Nice question. Totally makes sense to not allow a FK constraint using a temp table. I would have never thought to even attempt that. 😀

  • Brian.Klinect

    Mr or Mrs. 500

    Points: 592

    I was scared for a minute that this was actually valid, and wondered what would happen when the #temp table went away. Glad to see it is unpermitted.

  • Revenant

    SSC-Forever

    Points: 42467

    David Fundakowski (8/16/2016)


    I thought this was an exact copy of my question from 8/2! Didn't catch table2 was not temp. Need coffee....

    Yes, it was.

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

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