August 15, 2016 at 11:20 pm
Comments posted to this topic are about the item Primary and Foriegn Keys on Temp Tables
Thank You.
Regards,
Raghavender Chavva
August 15, 2016 at 11:31 pm
This was removed by the editor as SPAM
August 15, 2016 at 11:52 pm
Nice Question
Thanks
August 16, 2016 at 5:07 am
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.
August 16, 2016 at 5:33 am
ako58 (8/16/2016)
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.
August 16, 2016 at 5:36 am
This was removed by the editor as SPAM
August 16, 2016 at 6:18 am
ako58 (8/16/2016)
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:
August 16, 2016 at 6:29 am
Nice question. It illustrates a good point. Thanks.
August 16, 2016 at 6:55 am
Interesting. I've never tried this but it's good to know. Thanks.
August 16, 2016 at 7:04 am
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!
August 16, 2016 at 7:37 am
I thought this was an exact copy of my question from 8/2! Didn't catch table2 was not temp. Need coffee....
August 16, 2016 at 8:00 am
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
August 16, 2016 at 9:51 am
Nice question. Totally makes sense to not allow a FK constraint using a temp table. I would have never thought to even attempt that. 😀
August 16, 2016 at 1:00 pm
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.
August 16, 2016 at 1:51 pm
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 15 total)
You must be logged in to reply to this topic. Login to reply