locked
question about foreign key constraints RRS feed

  • Question

  • I have created a primary key constraint in one table in database A, now can I have a foreign key in another table which is in
    database B(different database)? Thanks.


    sqldev
    Friday, October 16, 2009 1:50 AM

Answers

  • You cannot have cross-database foreign keys but you can still implement the integrity check with triggers.  Be aware that a database restore can violate the referential integrity unless the backup/restores are synchronized. 
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, October 16, 2009 3:16 AM

All replies

  • You cannot have cross-database foreign keys but you can still implement the integrity check with triggers.  Be aware that a database restore can violate the referential integrity unless the backup/restores are synchronized. 
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, October 16, 2009 3:16 AM
  • It is possible only with in the same database . A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table

    Thanks, Leks
    Friday, October 16, 2009 3:24 AM
    Answerer
  • Yes cross database foreign keys are not supported out of the box in SQL Server. But in most of the production scenarios, it becomes a necessity. I came across the same scenario and this is how I have handled it:

    I have created a trigger on the DB1.Table1, with the MERGE statement (feature only in SQL 2008), which keeps both the tables in sync with each other at all times. It is fired whenever an Insert, Update or a Delete is made on the table. In my case DB2.Table2 will not be updated manually.

    Please advice me if this is solution is a good one. Also please elaborate on the issue mentioned above with backup/restores.

    Thanks,
    Aparajith
    Friday, October 16, 2009 4:39 AM
  • I don't see a problem using cross-database triggers as you have described to keep tables is separate databases on the same server synchronized. 

    The concern with a database restore is that you may have logical integrity problems unless both databases are restored to exactly the same point in time.  Consider the case where one of your databases is restored from yesterday's full backup and the other is not.  The tables will then be out-of-sync so you'll need to address the differences manually.  Be aware that in a recovery situation, you'll need to either restore both databases to the same point in time or establish a process to remediate the differences.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, October 17, 2009 2:44 PM