2

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

Rob
  • 45,296
  • 24
  • 122
  • 150
bleu
  • 87
  • 1
  • 2
  • 11
  • No, its not possible: http://stackoverflow.com/a/5166741/245676 – dillenmeister Feb 03 '12 at 12:00
  • No, it isn't possible, although there are some hacky workarounds, such as by using triggers to 'simluate' referential integrity http://stackoverflow.com/questions/1424327/foreign-keys-on-table-from-different-database – StuartLC Feb 03 '12 at 12:02

3 Answers3

3

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.

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • Its not possible, but you may implement custom mechanism by triggers.-> Any examples or turorials where I can find this..The problem is - you never can say that your backups are consistent.->Did not understand about backups – bleu Feb 03 '12 at 12:20
  • http://www.mssqltips.com/sqlservertip/1508/foreign-key-vs-trigger-referential-integrity-in-sql-server/ – Oleg Dok Feb 03 '12 at 12:21
  • http://msdn.microsoft.com/en-us/library/aa902684%28v=sql.80%29.aspx#sql_refintegrity_topic06 – Oleg Dok Feb 03 '12 at 12:22
1

In your scenario, you have 2 databases - Creating FK between databases isn't possible (even within same SQL server instance).

Even if you had these tables within one database, it would lead to circular refference.

Does that answer your question?

Sebastian Siek
  • 2,045
  • 17
  • 16
  • Even if you had these tables within one database, it would lead to circular refference. -- Did not understand this. What do you mean by circular reference – bleu Feb 03 '12 at 12:19
  • Sorry, I should have been more specific. Table A would reference table B and the other way round - that isn't possible as you would not be able to add any records to these tables. – Sebastian Siek Feb 03 '12 at 12:21
1

As per the comments. No, unfortunately you simply can't.

Could you have the table sin the same database but different schemas? That would allow the foreign key relationship.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Could you have the table sin the same database but different schemas? That would allow the foreign key relationship.->Any totorials or examples where I can find this . – bleu Feb 03 '12 at 12:21