Sql Server 2008 Cross-database table linking (relationships)

Question

Hi guys, I have a bit of an issue, and to be honest I don't think there's an answer, but I'll give it a try anyway.

So I have two databases [A]->Company and [B]->Product. Both databases have a Country table which is then linked to other tables in each individual database. The problem is that the data between the two Country tables is a complete duplicate. So, I essentially have to duplicate some of the relationships in each database, and maintenance on top of that is just difficult...

So, I'm curious is there a way to create a cross-database relationship between tables so I can have only one set of Country+Helper tables that govern both databases?

Thanks in advance!

Answer 1

Well you might want to think about having one transaction database, and two databases for data persistence? Lots of work, but it seems like the best viable option -- every transaction in the write would trigger a write to each of the "read" databases? I'm not sure I'm understanding the question completely, maybe?

Answer 2

You can't do this with foreign key contraints. You could enforce the relationship it with triggers, but it's a bit of a pain.

Answer 3

You can't have physical relationships that span multiple databases, to enforce FK constraints.

You can still have the core lookup/helper tables in one database, but you'd just have to have an inferred FK constraint on to it from the tables in the other databases - a logical assumption, rather than something physically constrained.

Answer 4

Keep in 1st database table & create a view from this table in 2nd database