Cross-database foreign key references posibility

Hi,

Here is the solution I posted for a previous question relating to the same thing:

You can't create a "solid" relationship between 2 tables that aren't in the same database. You can only do this with tables in the same database.
You have to add your own validation to your stored procedures to enforce referential integrity yourself, so although there is no physical relationship doing this for you, you do have a kind of relationship (which I call inferred foreign key constraints).

Example:
DBOne has a table tblOne
DBTwo has a table tblTwo

tblTwo has a field "strCode".
tblOne has a defined list of "strCode" with their descriptions.
When adding a new record to tblTwo, in your sp you need to check in DBOne.tblOne that a record exists with the supplied code. If it doesn't then raise an error. If it does, then it is ok to go ahead.

Cheers

Replace SecondDB by your second(PK) database name
and run in your FK(T2) database.


CREATE TRIGGER trT2_InsUpd ON dbo.T2 FOR INSERT,UPDATE AS
BEGIN
 if exists(select 'x' from inserted
                 left join SecondDB.dbo.T1 on inserted.Cust_Id=SecondDB.dbo.T1.Cust_Id
                     where SecondDB.dbo.T1.Cust_Id is null)
 begin
  RAISERROR ('FK T1.Cust_Id<-T2.Cust_Id violated !', 16, 1)
  ROLLBACK TRAN
 end
END

Raised points to 100

ispaleny:

I am trying to test your code. I am a little confused with secondDB thing you asked me to do. can you replace them with DB1 as the PK db
and DB2 as the FK db

Also what does inserted mean in your code:
left join SecondDB.dbo.T1 on inserted.Cust_Id=SecondDB.dbo.T1.Cust_Id
thax
Dhamijap

I created the trigger and made a mistake now I need to recreate it. Can you tell me what wiil be this for triggers


IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trT2_InsUpd]')
          and     OBJECTPROPERTY(id, N'IsUserTrigger') = 1)
     drop trigger [dbo].[trT2_InsUpd]

I need to put something like in front of my create stmt.
I am very close to find out if it works
thanks

You don't need to drop the trigger. Use ALTER TRIGGER

use DB2
GO
ALTER TRIGGER trT2_InsUpd ON dbo.T2 FOR INSERT,UPDATE AS
BEGIN
if exists(select 'x' from inserted
               left join DB1.dbo.T1 on inserted.Cust_Id=DB1.dbo.T1.Cust_Id
                   where DB1.dbo.T1.Cust_Id is null)
begin
RAISERROR ('FK T1.Cust_Id<-T2.Cust_Id violated !', 16, 1)
ROLLBACK TRAN
end
END
GO

I manully dropped it. And used your code adapted with diff name and it worked. But I like to add the similar if check in front of the create stmt. so that I do not have to bother with manually deleting it. If you do not know it is ok. I will be shortly awarding you the points.

Very good help from ispaleny. Keep up the good work. Thanks
Dhamijap

It is a longer standard version

IF exists
(
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[trT2_InsUpd]')
and OBJECTPROPERTY(id, N'IsUserTrigger') = 1
)

Names of objects are unique in one database, so you can check
IF OBJECT_ID(N'[dbo].[trT2_InsUpd]') IS NOT NULL

Or use an advanced undocumented version

IF OBJECT_ID(N'[dbo].[trT2_InsUpd]','TR') IS NOT NULL

All IFs check for an existence of trT2_InsUpd owned by dbo
and then they execute the nearest command (drop trigger ...).