Primary/Foreign Key relationships between tables in 2 Databases

  • Looking for some Database Guru's to help me out. Using SQL Server 2005 Enterprise is it possible to have a relationship between tables in two different DBs?

    In other words we are using 1 DB as a common threads aka data that can be used between applications. And they are used as lookups for the other DB(s).

    Please let me know if you need more details.

    Example (Countries)... If you did not want to add a countries table to all the DBs but have them stored in one central location.

    Thanks again.

  • You cannot enforce Foreign Keys across databases using DRI, you would have to do it in a trigger or check constraint (would have to be a UDF).

  • ty Jack...

  • DRI = ????

  • Declaritive Referential Integrity. Using FK's instead of the application or triggers.

  • What, if any, would be considered disadvantages for using table triggers to provide RI across different databases? I am presently designing a new system and was thinking of pulling out "core" information into a separate database but have "F/K" relationships to the core from the application database. Not a good idea?

    The other consideration is to replicate the core tables into the application database.

    Any thoughts?

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • You can do the RI using triggers and it is the only way to do it across databases. The issues are performance and the fact that triggers are "hidden" code that people tend to miss. I'd definitely rather have that than nothing.

  • Kurt W. Zimmerman (6/16/2010)


    I am presently designing a new system and was thinking of pulling out "core" information into a separate database

    Chances are there is no need to enforce such RI.

    Could you please elaborate a little more about "pulling out core information into a separate database"?

    Is the idea to move data from OLTP to DSS?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • This new application has a set of static tables (for the most part) that I'd like to keep in a separate database rather than have copies of them in the application database. The application database will be referencing these core tables where by the need of maintaining RI.

    The design concept is to have many application databases referencing the single core database. The design lends itself to minimizing the database footprint.

    So if I decide to scrap that idea then I may rely on replicating the core data to the application databases. Not the design I wanted except for the fact that it allows me to manage the core information in a single location vs. all of the application databases.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • You can use transactional replication from the "core" tables down to the satellite versions to maintain integrity locally when you insert, delete or update the core tables.

    Steve Hatchard

    Director

    Mattched IT Ltd

    http://www.mattchedit.com

  • That is exactly what I did and it works like a charm. Although not the design I was hoping for I don't think there is going to be much of an issue.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply