FK / PK relationships across databases?

I have an MS SQL 2008 R2 instance. There are 3 databases; I will say db_1, db_2 and db_3. I want db_2 to hold all the common information like users, email addresses, and some other metadata, then the other 2 db's will be for 2 separate front end transactional applications. Is there a way to enforce PK / FK constraints across databases in the same MS SQL server instance?
LVL 1
sqlagent007Asked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Is there a way to enforce PK / FK constraints across databases
No.  relationships are within a database only.

The closest you can get, and even this is somewhat of a house of cards, would be to write trigger code in one_db.one_table, testing to see if the ID is unique compared to other_db.other_tables, and if not either denying the insert or changing the ID.
0
 
Kamal KhaleefaInformation Security SpecialistCommented:
hi
i think it would be very difficult because your tables are in different databases

i suggest either to put all your tables in one database and do the PK/FK

or keep the design as it is and control every thing from you Code( to do insert / update /delete)
0
 
sqlagent007Author Commented:
Thanks experts!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.