13

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.

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
AJM
  • 32,054
  • 48
  • 155
  • 243
  • 1
    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? – Damien_The_Unbeliever Mar 02 '11 at 11:22
  • possible duplicate of [Add Foreign Key relationship between two Databases](http://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases) – Andriy K Mar 26 '15 at 17:15

2 Answers2

22

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.

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
  • Are you implying that a foreign key constraint can reference a table in another database by using a synonym as an intermediary? – Kenny Evitt Apr 30 '12 at 18:54
5

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.

Jeremy Gray
  • 1,378
  • 1
  • 9
  • 24