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 -
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.
-
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.
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.
-
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.
In the QotD of 2016/08/02 were the both tables listed as temporary #TableTest1 and #TableTest2, it can be easy to overlook. :unsure:
-
Nice question. It illustrates a good point. Thanks.
-
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.
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.
-
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply