October 19, 2006 at 9:26 am
Is it possible to create a foreign key between to distinct SQL databases ?
Database A contains table customers (primary key : customer_id)
Database B contains table invoices (each invoice should be linked to a customer. Foreign key : customer_id)
I would like to create an integrity constraint between invoices.customer_id and customers.customer_id, despite the fact that invoices et customers tables are in SEPARATE databases.
Is it possible to do that ?
Thanks in advance
Tom
October 19, 2006 at 11:33 pm
No, it's not possible.
October 20, 2006 at 1:27 am
Although Robert's correct in that there's no built in way to enforce this there are a couple of workarounds.
1. Create a trigger on the table to be updated which checks the other database upon create or update to ensure a "parent" record exists
or (my preference)
2. Create a user defined function to check another database for validity e.g.
CREATE FUNCTION [dbo].[ufn_CheckConstraint_Customer] (@CustomerID varchar(20))
RETURNS bit AS
BEGIN
DECLARE @retVal bit
IF EXISTS (SELECT * FROM CustomerDB.dbo.Customers WHERE CustomerID = @CustomerID)
SET @retVal = 1
ELSE
SET @retVal = 0
RETURN @retVal
END
Then in the invoices table add a check constraint to the column you need to check (e.g. CustomerID) with something like
([dbo].[ufn_CheckConstraint_Customer]([CustomerID]) = 1)
Any data you try to update or create in the invoices table will check for the existence of the CustomerID using the UDF in the other database.
Hope this helps
October 20, 2006 at 9:17 am
Or create a view that references the table in the other database and reference the view in the check constraint. There are always work arounds, but then the question becomes "should you?" not "can you?"
Any type of constraint or check you place on the column will roll back transactions on any failure. If your check or constraint references a database on another server and the server goes down for any reason, this table becomes unusable. Nothing can be inserted into it and all affected transactions will be rolled back. That's a very serious design issue.
Furthermore, these options do not offer the functionality of a foreign key such as cascading updates and deletes.
So, I would assert that there is no good alternative to a foreign key in this situation.
October 20, 2006 at 9:34 am
Robert,
I'm not sure I understand your responses.
Yes, if the check constraint references a linked server and the other server goes down inserts or updates will fail, but surely this is the same if the referenced database is suspect, for example ? Doing cross network referential integrity checks across a linked is a "very serious design issue" I agree but that was not the proposed problem in the original post.
Secondly the title of the post "Cross database integrity" indicates the primary question which was how to enforce an integrity check, which the proposed solution does. I agree that cascading updates and deletes are not included, but again, the original poster didn't ask for these and I'm not particularly convinced of their prevalence anyway.
So I would finally disagree with your last statement that what has been provided is not a "good alternative" to a foreign key as it provides the base functionality requested.
October 20, 2006 at 9:56 am
The initial question was can you specify a primary key, not how to work around it, which is part of the reasons why I didn't initially suggest any work arounds. The other part is that I do not like to suggest work arounds that I think may sound like a nice solution at the time but later cause them a lot of heart ache.
For the same reason that I wouldn't do a cross server check constraint, I wouldn't do a cross database check constraint. If your whole system can be taken down by a single database or single server going down, that is a serious problem.
Of course, with the addition of the try:catch construct in T-SQL in SQL 2005, your suggestion is much more viable.
November 14, 2006 at 9:24 am
I have faced the same problem and questions.
The solution noggin suggested didn't sound that bad to me either, and once I applied I felt quite "safe".
Of course, that was before - following your example - the client table data pk id's were overwritten manually by a copy paste of some stupid moron in my company. That was also before I discovered that you cannot mix check constraints with table scans since update stmts will fail unexpectedly (so triggers are a must).
So if you think about, there is no better technical answer then to avoid remote RI if possible. My key argument earlier was "yes, but what about the redudancy ?" but not each application wants to use the data in the same way, and how can you know that there will be no future application specific requirements about the shared table ?
Applying triggers on the clients table for cross-database RI would likely also prevent a number of corruption scenario's (like the one I mentioned above), but you'll never be absolutely sure nothing can go wrong.
So IMO scalability and data integrity score higher then a (likely OO influenced) tendency to reuse database components in a relational database.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply