May 12, 2009 at 7:17 am
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.
May 12, 2009 at 7:28 am
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).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2009 at 7:41 am
ty Jack...
May 12, 2009 at 7:51 am
DRI = ????
May 12, 2009 at 7:55 am
Declaritive Referential Integrity. Using FK's instead of the application or triggers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 16, 2010 at 8:21 am
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
June 16, 2010 at 8:31 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 16, 2010 at 8:43 am
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.June 16, 2010 at 9:43 am
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
June 17, 2010 at 12:17 pm
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
June 17, 2010 at 1:33 pm
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