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:
CREATE TABLE #tmp (A int NOT NULL);
INSERT INTO #tmp (A) VALUES (1);
SELECT A FROM #tmp;
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.