March 10, 2015 at 11:13 am
Very good question, Tom.
It was easy for me since I knew beforehand that the foreign key would be ignored and the temp table would be created.
Hope to see more questions from you.
---------------
Mel. 😎
March 17, 2015 at 5:20 am
From the explanation:
(there isn't any good reason for this, as the target of the foreign key isn't in a temp table, but for some reason temp tables are not allowed to reference ordinary tables)
There actually is a good reason. Temporary tables are stored in tempdb. Permanent tables are (normally) stored in a user database. And cross-database foreign key constraints are not supported in SQL Server.
March 17, 2015 at 8:08 pm
Hugo Kornelis (3/17/2015)
From the explanation:(there isn't any good reason for this, as the target of the foreign key isn't in a temp table, but for some reason temp tables are not allowed to reference ordinary tables)
There actually is a good reason. Temporary tables are stored in tempdb. Permanent tables are (normally) stored in a user database. And cross-database foreign key constraints are not supported in SQL Server.
Is there a good reason for not supporting cross-database foreign key constraints? For some reaon or other I believe there isn't.
Is there a good reaon for holding temp tables in tempdb so as to make the cross-db FK question relevant? I'm pretty sure there isn't.
Of course it's always easy to claim that some feature of the implementation as it stands would make it difficult and that that's a good reason, but that seems to me to be an extremely weak argument in all but a small proportion of cases.
Tom
March 18, 2015 at 3:29 am
TomThomson (3/17/2015)
Is there a good reason for not supporting cross-database foreign key constraints? For some reaon or other I believe there isn't.
How would you handle dropping a database, or setting it offline? Restoring one database to the state it had yesterday but not the other? Detaching it and then moving it to another server?
You could probably throw an error on all those scenarios (and a few more I have not thought of yet) if there is a cross-database foreign key, but espacially a ban on restoring would probably not fly well. The effect would be nobody using the feature.
And you would also have some quite interesting challenges with a foreign key on a character column if the databases have different collations.
Is there a good reaon for holding temp tables in tempdb so as to make the cross-db FK question relevant? I'm pretty sure there isn't.
Throwing lots of temporary objects in a user database would pose a nightmare for DBAs who try to do allocate and manage space and resources. What good are umpteen files for tempdb to prevent allocation contention if the allocation contention is moved to a user database?
Also, tempdb uses less logging because no recovery phase is required on system restart. Moving temp tables to user databases would hurt performance.
Moving temp tables to user databases would also break existing code in interesting ways. For example, this currently works:
USE Database1;
CREATE TABLE #tmp (A int NOT NULL);
INSERT INTO #tmp (A) VALUES (1);
USE Database2;
SELECT A FROM #tmp;
go
Of course it's always easy to claim that some feature of the implementation as it stands would make it difficult and that that's a good reason, but that seems to me to be an extremely weak argument in all but a small proportion of cases.
Most (but not all) of the problems I mention above can probably be solved in one way or another. But it would not be a small task. And even though it's true that Microsoft has deep pockets, that is mostly true because they monitor their expenses carefully - in other words, there are no infinite resources available for the SQL Server development team; choosing to implement a feature means that another feature will not be done.
When I make an estimate of the resources needed to enable foreign keys between temporary and permanent tables, and think of all the other features that could be implemented using those same resources, I see an incredibly easy choice.
March 18, 2015 at 3:14 pm
My main problem was that I miss the obvious fact that these were check boxes and not radio buttons.
Need to read more carefully I guess.
I ran the script and saw the warning. I also noted that there was a warning about the foreign key in the #temp table.
I knew that foreign keys are not allowed in temp tables but I was more interested(might not be all that interesting to those in the know) by what happened when I changed #temp to temp a permanent table.
Table junk is created, table temp is created the foreign key is created and the insert into table temp fails with an error.
(1000 row(s) affected)
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__temp__xptr__5B638405". The conflict occurred in database "XXXX", table "dbo.junk", column 'x'.
The statement has been terminated.
(1 row(s) affected)
XCOUNT 0
March 19, 2015 at 9:44 am
Hugo Kornelis (3/18/2015)
TomThomson (3/17/2015)
Is there a good reason for not supporting cross-database foreign key constraints? For some reaon or other I believe there isn't.How would you handle dropping a database, or setting it offline? Restoring one database to the state it had yesterday but not the other? Detaching it and then moving it to another server?
You could probably throw an error on all those scenarios (and a few more I have not thought of yet) if there is a cross-database foreign key, but espacially a ban on restoring would probably not fly well. The effect would be nobody using the feature.
So far as dropping a database is concerned, there's an identical problem with dropping a table, so that part of the argument for not having cross-database foreign keys is an equally good argument for not havig foreign keys at all.
A common reason for splitting something into two databases is that some data changes much more slowly than other data, so doesn't need to be backed up as frequently and backup performance can be improved. But currently it's often impossible to split things like this because the fequently changing data has references into the infequently changing data. In a case like that the restore thing isn't a genuine issue; in other cases it probably is, and DBAs should avoid setting up cross-database FKs that would cause an issue, or splitting databases so as to necessitate them (for integrity) in such cases. Setting a database offline is not an issue except perhaps when a temp table has references to it - it can just make any database other than tempdb involved with it in cross-db FKs go offline at the same time. After all, if all the tables were in one database to avoid cross-db FKs all the tables would go offline or online together (and all the tables would be restored together too, so perhaps in reality the restore thing is a non-issue too, but to me it looks as it it could be a real issue if DBAs were careless about it - but there are plenty of real issues with careless DBAs anyway). It would be more difficult to handle the case where tempdb points to something unless a rule were introduced that dropped temp tables pointing into databases taken off-line or restored; that would be pretty easy to live with, and not terribly difficult to implement.
And you would also have some quite interesting challenges with a foreign key on a character column if the databases have different collations.
There's no challenge there at all!
If a a key attribute has a certain collation then every column that references must have that collation, and the column should be declared appropriately (with a COLLATE clause if it's not the default collation) when created. Cross database FKs would make exactly no difference whatever to that. Just as now, if the referencing database has a default collation other than that of the target colum, then the refencing column must be created with a non-default collation - - the fact that the referencing database and the target database are one and the same database and hence have the same default collation does not change that rule , and if it's broken in the single database case the result will be two error messages (1776 followed by 1750) and those same two error messages would do just fine in the cross-database case.
Is there a good reaon for holding temp tables in tempdb so as to make the cross-db FK question relevant? I'm pretty sure there isn't.
Throwing lots of temporary objects in a user database would pose a nightmare for DBAs who try to do allocate and manage space and resources. What good are umpteen files for tempdb to prevent allocation contention if the allocation contention is moved to a user database?
Also, tempdb uses less logging because no recovery phase is required on system restart. Moving temp tables to user databases would hurt performance.
You're right, my brain clearly wasn't working properly when I wrote those two sentences!
Tom
March 19, 2015 at 11:09 am
TomThomson (3/19/2015)
So far as dropping a database is concerned, there's an identical problem with dropping a table, so that part of the argument for not having cross-database foreign keys is an equally good argument for not havig foreign keys at all.
Except that the system is currently architected to guard those relationships within a single database, not cross-database. Which can be changed of course, but that brings us back to the discussion of priorities within the limited resources.
A common reason for splitting something into two databases is that some data changes much more slowly than other data, so doesn't need to be backed up as frequently and backup performance can be improved.
Filegroups are just the right tool for that purpose. Much better (IMO) than spreading the data across databases.
Setting a database offline is not an issue except perhaps when a temp table has references to it - it can just make any database other than tempdb involved with it in cross-db FKs go offline at the same time.
Which again requires engineering effort. And I am sure would raise eyebrows - many people will not appreciate database X going offline if they put database Y offline.
And tempdb would not be the only exception, master and msdb would be exceptions too. Which makes the whole proposition a bit fickle. ("We have a system that runs fine,e xcept in a lot of cases wherer it doesn't" ...)
And you would also have some quite interesting challenges with a foreign key on a character column if the databases have different collations.
There's no challenge there at all!
You are right, I was looking at default collation, but one can of course always override that.
March 25, 2015 at 3:11 am
The below script gives a hint that both the tables will be created successfully
drop table junk, #temp;
so two answers are partially revealed, only one more option to check 🙂
March 26, 2015 at 5:36 pm
pmadhavapeddi22 (3/25/2015)
The below script gives a hint that both the tables will be created successfullydrop table junk, #temp;
so two answers are partially revealed, only one more option to check 🙂
I must remember to use a drop statement that fails because the table wasn't created in a question some time - with the drop statement being at the end after all the statements the question asks about :alien:
Tom
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply