Foreign Key Relation Across Database
Foreign Key Relation Across Database
Foreign key references can be found across the database with a simple Microsoft SQL Server code. This code will work between the tables to get the keys.
Join the DZone community and get the full member experience.Join For Free
Download "Why Your MySQL Needs Redis" and discover how to extend your current MySQL or relational database to a Redis database.
We all know about the foreign key reference. But what happens when we want foreign key references across the Database? This article shows you how. Hope it will be informative.
Understand the Case
To understand it properly, we are trying to make a pictorial diagram.
Here we have a Microsoft SQL Server named DB Server that has two Database named DB1 and DB2. The DB1 database has a table named Table1 and DB2 database has a table named Table2. We are trying to make the Foreign Key relation between the Table1 and Table2.
Let’s Take an Example to Understand It
Step-1 [ Creating the base table ]
USE [DB1]; GO IF OBJECT_ID(N'[dbo].[tbl_ITEM]', N'U')IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_ITEM]; END GO CREATE TABLE [dbo].[tbl_ITEM] ( ITEMCODE INT NOT NULL PRIMARY KEY, ITEMMNAME VARCHAR(50) NOT NULL ); GO INSERT INTO [dbo].[tbl_ITEM] (ITEMCODE, ITEMMNAME) VALUES (101, 'Tooth Paste'), (102, 'Tooth Brush'), (103, 'Saving Lootion'); GO ITEMCODE ITEMMNAME ----------- -------------------------------------------------- 101 Tooth Paste 102 Tooth Brush 103 Saving Lootion (3 row(s) affected) USE [DB2]; GO IF OBJECT_ID(N'[dbo].[tbl_ITEMORDER]', N'U')IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_ITEMORDER]; END GO CREATE TABLE [dbo].[tbl_ITEMORDER] ( ORDERNO INT NOT NULL IDENTITY PRIMARY KEY, ITEMCODE INT NOT NULL, QTY DECIMAL(18,2) NOT NULL ); GO
Step-2 [ Try to Creating the Foreign Key Relation ]
ALTER TABLE [dbo].[tbl_ITEMORDER] ADD CONSTRAINT FK_ITEMCODE_tbl_ITEMORDER FOREIGN KEY(ITEMCODE) REFERENCES [DB1].[dbo].[tbl_ITEM](ITEMCODE);
It gives the error:
Msg 1763, Level 16, State 0, Line 1 Cross-database foreign key references are not supported. Foreign key 'DB1.dbo.tbl_ITEM'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
So the Cross Database Foreign key Relation is not possible Directly.
Step-3 [ What's the Solution ]
We can do the something by CHECK Constraint. Here the example is.
First we create a Function:
IF OBJECT_ID(N'[dbo].[func_CHECK_ITEMCODEREFERENCE]', N'FN')IS NOT NULL BEGIN DROP FUNCTION [dbo].[func_CHECK_ITEMCODEREFERENCE]; END GO CREATE FUNCTION [dbo].[func_CHECK_ITEMCODEREFERENCE] ( @p_ITEMCODE INT = 0 ) RETURNS INT AS BEGIN DECLARE @v_RetVal INT; SET @v_RetVal = 0; IF EXISTS(SELECT * FROM [DB1].[dbo].[tbl_ITEM] WHERE ITEMCODE = @p_ITEMCODE) BEGIN SET @v_RetVal = 1; END RETURN @v_RetVal; END
Use the function in Alter statement CHECK Constraint.
ALTER TABLE [dbo].[tbl_ITEMORDER] ADD CONSTRAINT CHECK_ITEMCODE_tbl_ITEMORDER CHECK([dbo].[func_CHECK_ITEMCODEREFERENCE](ITEMCODE)=1);
Step-4 [ Now Check it ]
INSERT INTO [dbo].[tbl_ITEMORDER] (ITEMCODE, QTY) VALUES (101, 200);
It inserted perfectly as the ITEMCODE 101 is present in the Table TBL_ITEM in the Database DB1.
INSERT INTO [dbo].[tbl_ITEMORDER] (ITEMCODE, QTY) VALUES (110, 200); Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "CHECK_ITEMCODE_tbl_ITEMORDER". The conflict occurred in database "DB2", table "dbo.tbl_ITEMORDER", column 'ITEMCODE'. The statement has been terminated.
So it is working like Foreign Key Constraint and hence the Solution is.
Hope you like it.
Published at DZone with permission of Joydeep Das , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.