August 2, 2016 at 9:47 am
Yes, this should definitely be at least a warning, not an info, but that is what we are stuck with. But it is a nice question - thanks, David!
August 2, 2016 at 10:21 am
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 ?
Good point! Heck, why would one use Foreign Keys in any tables? Same difference right???
August 2, 2016 at 12:45 pm
patrickmcginnis59 10839 (8/2/2016)
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 ?
Good point! Heck, why would one use Foreign Keys in any tables? Same difference right???
Actually it would be a good idea not to allow permanent tables to have foreign keys pointing to temporary tables, but I can see no logical issue with foreign keys from a temporary table to a permanent table (provided the destruction of the temporary table when it is no longer in scope automatically drops the foreign key relationship).
A foreign key from one temporary table to another is a little harder unless the target of the foreign key can't be destroyed (by ceasing to be in scope or by a drop command) until the foreign key constraint is dropped (by dropping it directly or by destruction of the referring table).
Perhaps it would have to work for global foreign keys as well as temporary ones, which might lead to a local temp table that is the reference of a foreign key in a global temp table having to continue to exist after the session in which it was created had terminated (unless global temp tables were forbidden to refer to local temp tables). So you can see that it would be possible to do it, although there are some choices to be made and some difficulties.
Tom
August 3, 2016 at 6:28 am
TomThomson (8/2/2016)
patrickmcginnis59 10839 (8/2/2016)
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 ?
Good point! Heck, why would one use Foreign Keys in any tables? Same difference right???
Actually it would be a good idea not to allow permanent tables to have foreign keys pointing to temporary tables, but I can see no logical issue with foreign keys from a temporary table to a permanent table (provided the destruction of the temporary table when it is no longer in scope automatically drops the foreign key relationship).
A foreign key from one temporary table to another is a little harder unless the target of the foreign key can't be destroyed (by ceasing to be in scope or by a drop command) until the foreign key constraint is dropped (by dropping it directly or by destruction of the referring table).
Perhaps it would have to work for global foreign keys as well as temporary ones, which might lead to a local temp table that is the reference of a foreign key in a global temp table having to continue to exist after the session in which it was created had terminated (unless global temp tables were forbidden to refer to local temp tables). So you can see that it would be possible to do it, although there are some choices to be made and some difficulties.
Yep, mixing permanent and temporary tables would be interesting to say the least!
August 3, 2016 at 7:03 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply