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.

Obsequent

The problem is, the unit of recovery with SQL Server is the database - so what do you do if the two databases are restored with inconsistent data (e.g. from different points in time), or if one database is offline? – Ikeikebana

possible duplicate of Add Foreign Key relationship between two Databases – Deas

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.

Peaslee

Are you implying that a foreign key constraint can reference a table in another database by using a synonym as an intermediary? – Vendee

Answer 2

It's a lot of work but you may think about merging those two databases into a single database. If you want a logical difference between objects within the database, you can use a schema.

Delineation