Creating a relatioship between 2 databases

• Juanita

  SSCertifiable

  Points: 5300

  More actions

  August 4, 2006 at 3:17 pm

  #61598

  HI. I have a developer that wants to create a relationship between 2 tables in 2 different databases. I know in table designer that cannot be done because when creating relationships it will only tables from 1 database. Is there a way to accomplish this via another route?

  thanks,

  Juanita

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  August 4, 2006 at 4:24 pm

  #653445

  Usually I've seen this controlled via triggers.

• Jeff Moden

  SSC Guru

  Points: 1004392

  More actions

  August 4, 2006 at 7:56 pm

  #653459

  Spot on... I absolutely agree with Aaron.  In fact, I don't know of any other way to establish DRI between two different databases.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sqldba-294117

  Hall of Fame

  Points: 3486

  More actions

  August 7, 2006 at 3:49 pm

  #653743

  I agree to above two having triggers where you need triggers on both tables. On the parent to check deletes and updates, and one of the child to check insert and updates.

  Worst suggestion is about Replication Pub/Sub then add FK on SUB. but still i will not recommend.

  i am not confortable to implement a PK / FK relationship via CHECK constraints that use UDFs for both tables on 2 different DB.

  Follow this link for SQL 2005 in refrence to DRI:-

  http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4483c26cdd3e2e1e/b2b74c0aa3266eaa?lnk=st&q=&rnum=3&hl=en#b2b74c0aa3266eaa

  Thanks