Referential constraint between two tables in two databases

Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result.

i think not possible

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

You should be able to set up the reference as a linked server (http://www.databasejournal.com/features/mssql/article.php/3085211,http://msdn2.microsoft.com/en-us/library/ms188279.aspx -- this is a sql server 2005 article, but earlier versions support linked servers as well)

I doubt that you can enforce the constraint declaritively, but I am quite sure you can do it using a trigger. Let us know if you need help with the triggers.

|||

And what can be used when databases are sitted on the same server?

|||

Then it's even easier because you don't need the linked server, you can simply reference the table on the other database by indluding the DB name, eg,

database2.dbo.sometable

Or (and this is the way I do it), create a view in database1 pointing to the table in database2

use database1
go
create view TableXyz as
select * from databse2.dbo.TableXyz
go
grant insert, update, select, delete on TableXyz to ...
go

Then, from within database1, all references to TableXyz really point to database2.dbo.TableXyz

I think the declaritive RI may work in this scenario but I'm not positive, but in any case a trigger will work

sql