CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Gup3rSuR4c
Gup3rSuR4c

Reputation: 9490

Sql Server 2008 Cross-database table linking (relationships)

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!

Upvotes: 3

Views: 2630

Answers (5)

gmkishor
gmkishor

Reputation: 1

Yes. Keep Country table in the 1st database and create a view of country table of database1 to the database2. No physical data is there only view so no need to panic.

Upvotes: 0

Roman Spivak
Roman Spivak

Reputation: 1

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

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147234

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.

Upvotes: 4

Ray
Ray

Reputation: 21905

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.

Upvotes: 0

Bryan A
Bryan A

Reputation: 3634

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?

Upvotes: 2

Related Questions