How to Set Relationship in Database. [Resolved]

Posted by Jayakumars under C# on 9/8/2016 | Points: 10 | Views : 245 | Status : [Member] [MVP] | Replies : 1
Hi

How to Add Foreign Key relationship between two Databases.

I have two tables in two different databases. In table1(It is in database1) there is a column called ID and it is a primary key. Now in table2(It is in databse2) there is a column called Table1ID and I want to add it as a foreign key.

I tried to add it and it gave me the following error:

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key Database2.table2.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 9/14/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
It is clearly displaying the message "Cross-database foreign key references are not supported. ". The alternate way to do so is to create trigger e.g.

Create Trigger database1.dbo.MyTableTrigger ON database1.dbo.table1, After Insert, Update
As
Begin

If NOT Exists(select PK from databse2.dbo.table2 where PK in (Select FK from inserted) BEGIN
-- catch the referential integrity error
END

END


Hope that helps


--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response