Primary and Foriegn Keys on Temp Tables

  • 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

  • Nice Question

    Thanks

  • 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

  • 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

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

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

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

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

  • 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

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

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

  • 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