CODEWITHSUNDEEP
sql-server-2008foreign-keysconstraints
chok68
chok68

Reputation: 933

Foreign key in SQL Server 2008 pointing to another database

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!

Upvotes: 0

Views: 5343

Answers (3)

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 2

marc_s
marc_s

Reputation: 754388

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.

Upvotes: 2

Matthew
Matthew

Reputation: 10444

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?

Upvotes: 3

Related Questions