SQL Server Foreign Keys across database boundaries - techniques for enforcement

Question

I have two separate SQL Server 2005 databases (on the same server)

• security database

• main application database

• The security database has a user table with everything needed to authenticate. -

• The application database has a person table with extended user details. There is a 1-1 mapping between the security database user table and the application database person table.

I want to enforce a mapping between the user and the person table. I'm assuming that foreign keys can't be mapped across databases thus I am wondering what to do to enforce the integrity of the relationship.

Answer 1

Cross database foreign keys are indeed not supported

    Msg 1763, Level 16, State 0, Line 2
    Cross-database foreign key references are not supported.

If you really want to enforce the referential integrity on the database side you will have to rely on triggers. (which I don't recommend)

to make your code more maintainable you could create synonyms for the tables you want to check referential integrity on.

      CREATE SYNONYM myTable FOR otherdatabase.dbo.myTable;

This would be to make the "manual" checks easier, as you can not create foreign keys on a synonym.