Search code examples
sqlsql-server-2008r2-express

Having Foreign Key relations between two different columns in two different databases

I have two databases A and B. A has table tableA with columnA similarily B has tableB with coulmnB. Can I have a Primary , Foreign Key relationship between these two columns. Both databases will be in the same Sql Server 2008 R2 instance.

I am using Sql Server 2008 R2 Express Edition.

Also if this is not available in express edition then is it available in other editions such as enterprise

Solution

  • Its not possible, but you may implement custom mechanism by triggers.

    The problem is - you never can say that your backups are consistent.

    Since referential integrity implemented with FOREIGN KEY constraint guarantees that all your data are valid after the transaction ends and your backups always be consistent.

    With different databases and trigger-based ref. integrity you never can say that both databases backed up simultaneously and in consistent state.