Add a foreign key between tables of 2 databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add a foreign key between tables of 2 databases

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-05-19 : 16:40:55

Hi All,
is there a way I can add a foreign key between tables present in 2 different databases.
suppose d1 and d2 are the databases.
table t1 is present in d1.
table t2 is present in d2.
is it possible to have a foreign key between t1 and t2?

Thanks in anticipation,
Mavericky

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 16:48:41

No, you'll have to create a trigger to manage that, or move a table to the other database.

mavericky
Posting Yak Master

117 Posts

Posted - 2011-05-19 : 16:56:13

Thanks robvolk. Can you tell me how to create such a trigger or some example i can refer to?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 17:11:27

[code]USE D2
GO
CREATE TRIGGER T2_Insert_Update_FK ON T2 FOR INSERT, UPDATE AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM inserted i
WHERE NOT EXISTS(SELECT * FROM D1..T1 WHERE IDCOL=i.IDCOL))
BEGIN
RAISERROR('Violation of pseudo-foreign key.', 16, 1)
ROLLBACK TRAN
END
GO

USE D1
GO
CREATE TRIGGER T1_Delete_FK ON T1 FOR DELETE AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM deleted d
WHERE EXISTS(SELECT * FROM D2..T2 WHERE IDCOL=d.IDCOL))
BEGIN
RAISERROR('Violation of pseudo-foreign key.', 16, 1)
ROLLBACK TRAN
END
GO[/code]The second trigger protects against deleting rows from T1 that exist in T2, and is optional.

Another option you might consider is moving T2 to D1 and creating a synonym in D2 for it:[code]USE D2
GO
CREATE SYNONYM T2 FOR D1..T2[/code]You can insert into the synonym and it will go into the correct table in D1. This requires SQL Server 2005 or higher. The advantage is you can create a proper foreign key. The downside is if you had related tables in D2 you can't create foreign keys to the synonym.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-19 : 17:17:18

Hey,

Rob can you help me...my Lawn needs mowing

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 17:23:48

Sure, I'd love to mow a lawn under 1/2 acre in size.

BTW, I have 12-18 months of NO POSTS AT ALL to make up for.

You had all of 2006-2007 to yourself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources