Foreign key in SQL Server 2008 pointing to another database

Question

How can I add a constraint that references a foreign column from another database?

Some time ago I read that it can be done with linked server and others say with triggers. What's the preferred way of doing this if that's possible at all?

Thanks!

score 3 · Answer 1 · edited May 23 '17 at 10:26

3

Linked servers will not work.
FK's must point to local tables.
The preferred way is not to do this, though you can pull a few hacks to make it happen.
For example you could have triggers cause the reference to be created/checked but I wouldn't consider that equivalent to a FK constraint.

Related question: Can you have a Foreign Key onto a View of a Linked Server table in SQLServer 2k5?

edited May 23 '17 at 10:26

Community

1
1

answered Aug 23 '11 at 18:40

Matthew

10,244
5
49
104

Nice easy to follow rules! I was talking about _preferred_ because project specs state that some specific tables must reside outside a main database. I think I'll make a mix of your suggestion about triggers, and @HLGEM ideas about allowing multiple inserts/deletes, etc. and an exception table to make it auditable. – chok68 Aug 24 '11 at 16:51
@chok can they be on a different DB on the same machine? – Matthew Aug 26 '11 at 21:08

score 2 · Answer 2 · answered Aug 23 '11 at 18:45

Foreign keys cannot go across database boundaries. If you try to do this, you'll get:

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key ***
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

If you need to enforce some kind of relationship between two separate databases, then yes - you might need a linked server (if that second database is on a second server), and possibly triggers - but all of those things will be very hard to get right, very inefficient and very error prone.

One way you might be able to do this would be data replication - replicate the table you want to reference into your source database, and then establish a foreign key relationship with that replicated table. But that will never be quite "live" and "real-time" - there will also be a bit of a lag in the data replication.

score 2 · Accepted Answer · answered Aug 23 '11 at 19:07

Use an instead of trigger (you may or may not need linked servers depending on whether the other database is on a differnt server). Make sure the trigger can handle multiple row inserts/updates/deletes. I'd also suggest moving the records that fail the check to an exception table.

Foreign key in SQL Server 2008 pointing to another database

3 Answers3