?
Solved

Cross-database foreign key references posibility

Posted on 2003-03-27
9
Medium Priority
?
252 Views
Last Modified: 2012-06-21
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
Comment
Question by:dhamijap
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 8218511
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 8218625
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
 

Author Comment

by:dhamijap
ID: 8219102
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Accepted Solution

by:
ispaleny earned 400 total points
ID: 8219429
"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
 

Author Comment

by:dhamijap
ID: 8219669
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 8219722
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
 

Author Comment

by:dhamijap
ID: 8219772
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
 

Author Comment

by:dhamijap
ID: 8219812
Very good help from ispaleny. Keep up the good work. Thanks
Dhamijap
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8219881
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question