Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Cross Database Integrity Checks Expand / Collapse
Posted Tuesday, November 13, 2007 1:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 27, 2015 7:47 AM
Points: 35, Visits: 179

This is a question about maintaining the data integrity within the database system I'm using.

Currently I have a User table, that doesn't change much, but several different programs use it.

Essentially what I'd like to do is have tables in one database use a foreign key on a column that is in a separate database.

An example:

Database 1: "CommonDb"
Contains Table: UserInfo
Columns: Login_Name(PK), Full_Name, User_Title

Database 2: "Sales_Store"
Contains Table "Sales"
Columns: Sale_Id(PK), TimeStamp, AddedByUser, Amount

Database 3: "Account"
Contains Table "Accounts"
Columns: Account_Id(PK), TimeStamp, Account_Manager, Current_Balance

And the Idea would be that the "AddedByUser" field and the "Account_Manager" field would use the user LoginName from the CommonDb.

Perhaps there is already a good way of doing this, I'm not to sure, but any thoughts and suggestions would be welcome.

Post #421761
Posted Tuesday, November 13, 2007 3:43 PM


Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
MS SQL does not really support this.

This is not a typical thing to do. I remember seeing a product that someone sells to do this. I cannot remember what it is called.

You can use a combination of check constraints and triggers (or just triggers) to do this. Check constraints are generally against just the single table, but they can execute functions and functions can look at other tables and even be cross-database.

I would recommend doing this sparingly and be careful to see what it is doing to your database performance. I have implemented it a few times (and am actually implementing it on an application being deployed now), but it takes some careful planning or it can lead to problems.

I tend to put every bit of data integrity I possibly can into indexes, constraints, and triggers (triggers only when absolutely necessary) rather than doing the same thing in application layers or in stored procedures. This can be argued against for a lot of reasons - sone of them pretty good reasons at times, but I have found a couple of things true.
First, if you are doing something in an insert or update stored procedure that relates to data integrity, you can probably do the same thing with constraints or triggers and get the same performance at the same time eliminating the ability to bypass the logic by not using the stored procedures.
The second thing is that there will be a time in which your data will need to be modified with a tool other than the application with all of your business logic designed for your data. Argue all you want, but some new thing will come up in which someone needs to bulk load a bunch of data with SSIS or update a group of records with a query and the dope that does it will not know about the business rules programmed into the application. It would be nice to not hire that guy in the first place, but it is sometimes me, so I have to forgive him.

Please, nobody beat me up on this one. Your argument is good and I agree with many of your points.
Post #421819
Posted Wednesday, November 14, 2007 10:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 27, 2015 7:47 AM
Points: 35, Visits: 179

Since this constraint isn't required, but more of something that I'd like to have, I'll probably pass on it. It'll probably needlessly complicate things.

But on the other side of the coin, I know how to implement it now.

Thank you
Post #422206
Posted Wednesday, November 14, 2007 7:25 PM

Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 16, 2016 11:44 AM
Points: 6,639, Visits: 1,905
Is there a reason you can't collapse the databases into a single database?

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security),
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #422415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse