Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DrkMaster
Starting Member
11 Posts |
 Posted - 2004-02-20 : 17:33:38
|
| I am building two database on the same SQL Server. I have two tables, namely [Vendor] and [Manager], created in the first database called (AIT). I have another table called (Contract) on the other database called (CIT). I need two foreign keys in the [Contract] table to reference the other tables [Vendor] and [Manager].When I tried creating the SQL Query that would create them. it gave me the error message:Cross-database foreign key references are not supported.How do I get around that error?The reason the FKs are needed like this is that I do not want to have two separate tables of managers and vendors when the same information will be used by both databases. Any help will be appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-20 : 17:35:34
|
| You can not create foreign keys across a database. You will need to implement referential integrity using triggers.I believe the next version of SQL Server allows the cross database relationships to be created.Tara |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-21 : 11:59:32
|
| Well, I can't find anything in the Yukon beta material to suggest that, and I can't think of a bigger disaster than allowing cross-database foreign keys.Suppose you have a user table in Database A and you want to share it with Database B, C, etc. If Database A goes offline, you've brought down all of the others as well. You can't restore Database A without taking the others down, and with a high risk of violating the foreign key constraints if the restored data does not contain data referenced by the other databases. You also can't move A to another machine unless you move B, C, etc. Naturally, everyone will then want to allow cross-server foreign keys, which then have to contend with network connection as part of the data integrity process.This is essentially replication, not foreign key declarations. Either that, or the two databases should be combined into one. If you really can't combine them, then Tara's trigger suggestion is a good choice. |
|
|
|
DrkMaster
Starting Member
11 Posts |
Posted - 2004-02-24 : 16:14:09
|
| How should I go about installing replication to perform this task. I just need to import the data for the Vendor Table Only. Would I create a DTS Package and use a trigger to start it? OR what would you suggest? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-24 : 22:46:34
|
| If the databases are on the same server, just use INSERT INTO....SELECT syntax, using the fully-qualified database.owner.object name. No reason to use DTS for this. You can even do it across servers if you set them up as linked servers. See Books Online under "INSERT" for the exact syntax. |
|
|
|
|
|
|
|
|
Cross-Database References