SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cross Database Integrity Checks


Cross Database Integrity Checks

Author
Message
Kris-420132
Kris-420132
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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.

Kris
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12805 Visits: 23078
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.
Kris-420132
Kris-420132
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (24K reputation)

Group: Moderators
Points: 24144 Visits: 1917
Is there a reason you can't collapse the databases into a single database?

K. Brian Kelley
@‌kbriankelley
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search