Y'all are a bunch of wankers!

cross-database foreign keys

Looks like SQL Server (2005) doesn't support cross-database foreign keys, even if the databases are on the same server.
:-(

Anyone got any experience with a work-around?  I gather you can do it via triggers, but to me that seems like a bag.
Permalink xampl 
March 11th, 2007 7:57pm
I can't help you, but just out of curiosity, why would you want to set a foreign key in an entirely different database?
Permalink Bluebeard 
March 12th, 2007 6:48am
That's why one DB is better.

Sounds to me like you'll need to make a third DB, and then pull in the relevant tables from the other two DB's, and make your query on the third DB.
Permalink LinuxOrBust 
March 12th, 2007 7:29am
>> I can't help you, but just out of curiosity, why would you want to set a foreign key in an entirely different database? <<

The idea is to maintain a 1-deep change history on the business entity tables that show who made the change (created-by, modified-by columns).  The user table is (currently) in another database on the same server.
Permalink xampl 
March 12th, 2007 8:39am
I still don't understand why some people think it's an utter necessity to do foreign-key constraints in the DBMS itself.

Yes, it's a nice luxury, but can easily be done in software as long as you don't work with complete assholes.

Triggers piss me off even more than foreign-key constraints.  If half the application is in the fucking database, the application becomes harder to troubleshoot by an order of magnitude.
Permalink Send private email muppet 
March 12th, 2007 12:05pm
or you could write "if exists (select *..." in your insert/update/delete stored procs...
Permalink Kenny 
March 12th, 2007 1:47pm
I'd opt for Kenny's suggestion over triggers.

Hidden little nasty bits of demon code, that's what triggers are.
Permalink Send private email JoC 
March 12th, 2007 1:51pm

This topic is archived. No further replies will be accepted.

Other topics: March, 2007 Other topics: March, 2007 Recent topics Recent topics