Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Cross-database foreign key references posibility

I have two database on the same server ie. DB1 and DB2

I have T1 in DB1 with PK Cust_Id

I have T2 in DB2 with column Cust_Id

NOW: I want to create a FK which will reference to the DB1.T1.Cust_Id

Can I do that? = 50 point (if ans is No)

If I can you show me the line of code = 100 points (if ans is yes, I will raise them)

I am using SQL2000
0
dhamijap
Asked:
dhamijap
  • 4
  • 4
1 Solution
 
adatheladCommented:
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
0
 
ispalenyCommented:
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
0
 
dhamijapAuthor Commented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ispalenyCommented:
"FROM A LEFT JOIN B ON A.X=B.X" means:
Join at least one record from table B to every cond.matching record from table A, where exists no such a record, join null values.
So with adding "WHERE B.X IS NULL" I get all records from table A not matching a join condition.
This is your code.


use DB2
GO
CREATE 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


0
 
dhamijapAuthor Commented:
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
0
 
ispalenyCommented:
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
0
 
dhamijapAuthor Commented:
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.
0
 
dhamijapAuthor Commented:
Very good help from ispaleny. Keep up the good work. Thanks
Dhamijap
0
 
ispalenyCommented:
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 ...).
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now