Monday, August 5, 2013

Foreign key Relation Across Database

Foreign key Relation Across Database


Introduction

As we all know about the foreign key reference. But what happens when we want foreign key references across the Database. This article is related to it. 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 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 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')ISNOT 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.



Foreign Key Refers the Columns of Same Table

Introduction

We all know about the Foreign Key constraint. Here in this article, we are not going to discuss about the definition or implementation concept of Foreign Key. But here we are trying to discuss about the Foreign key that refers the another a column of same table.

What is That?

EMPID
EMPNAME
DESIGNATION
MANAGERID
101
Sudip Das
Manager
101
102
Joydeep Das
Group Lead
101
103
Sukamal Jana
Group Lead
105

Here EPID is the Primary Key and the MANAHERID is the foreign key which refers the EMPID of the same table.

In the above situation Employee ID 101 is in manager position, so its Manager ID is same 101. But for Employee ID 102 the Manager ID is 101 and we can insert data without any error.

But in case of Employee ID 103 if we provide the Manager ID 105 it gives us an error as no such employee id (105) is not present and Foreign key gives us an Error over there.

How we implement That

Step-1 [ The Base Table with Foreign Key References ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEEMASTER', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_EMPLOYEEMASTER];
   END
GO
CREATE TABLE [dbo].[tbl_EMPLOYEEMASTER]
    (
        EMPID        INT          NOT NULL PRIMARY KEY,
        EMPNAME      VARCHAR(50)  NOT NULL,
        DESIGNATION  VARCHAR(50)  NOT NULL,
        MANAGERID    INT          NOT NULL,
        CONSTRAINT FK_MANAGERID_tbl_EMPLOYEEMASTER FOREIGN KEY(MANAGERID)
        REFERENCES [dbo].[tbl_EMPLOYEEMASTER](EMPID)
      );

Here just look at the definition of foreign key specially the REFERENCES section.

Step-2 [ Insert Firs Record ]

INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
       (EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (101, 'Sudip Das', 'Manager', 101);
GO

Here we not find any error as Manager ID is the Same as the Employee ID and Foreign key Satisfied.

Step-3 [ Insert Second Record ]

INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
       (EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (102, 'Joydeep Das', 'Group Lead', 101);

GO

Here we do not get any error as Manager ID 101 is present in the table and Foreign key satisfied.

Step-4 [ Insert Third Record ]

INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
       (EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (103, 'Sukamal Jana', 'Group Lead', 104);     

GO

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_MANAGERID_tbl_EMPLOYEEMASTER". The conflict occurred in database "PRACTICE_DB", table "dbo.tbl_EMPLOYEEMASTER", column 'EMPID'.
The statement has been terminated.

Here it gives error as Manager ID 104 is not present in the Table.

Hope you like it.




Is Conditional Foreign Key is Possible

Introduction

One of my friends wants a conditional foreign key. I suppressed that is possible? Before proceed let’s see the case study.

Case Study
We have an Employee Master Table calledtbl_EMPMASTER which contains designation for both team lead and programmer.

Now we are going to create two children Table and use foreign key with master table. We want the Team Lead (TL) designation holder stores in table called tbl_EMP_TLand the Programmer (PRG) designation holder stores on tbl_EMP_PRG.

We must use foreign key for referential integrity.

Here is the Pictorial Diagram of Case study



Solutions
/*
   Logical Conditional Foreign Key
*/

IF OBJECT_ID(N'dbo.tbl_EMPMASTER', N'U') IS NOT NULL
   BEGIN
      DROP TABLE dbo.tbl_EMPMASTER;
   END
GO

CREATE TABLE dbo.tbl_EMPMASTER
       (
         EMPID        INT         NOT NULL PRIMARY KEY,
         EMPNAME      VARCHAR(50) NOT NULL,
         EMPDESIG     VARCHAR(10) NOT NULL,
         EMPDSGSTAT   AS CASE WHEN EMPDESIG='TL'  THEN 1
                              WHEN EMPDESIG='PRG' THEN 2
                                     END PERSISTED,
             CONSTRAINT UK_EMPID_EMPDSGSTAT UNIQUE(EMPID, EMPDSGSTAT)
         );                         

-- Inserting Master Data
INSERT INTO dbo.tbl_EMPMASTER
       (EMPID, EMPNAME, EMPDESIG)
VALUES (1, 'Joydeep Das', 'TL'),
       (2, 'Sangram Jit Bhattacharya', 'PRG');      
                                            
GO
IF OBJECT_ID(N'dbo.tbl_EMP_TL', N'U') IS NOT NULL
   BEGIN
      DROP TABLE dbo.tbl_EMP_TL;
   END
GO
CREATE TABLE dbo.tbl_EMP_TL
       (EMPID     INT           NOT NULL PRIMARY KEY,
        EMPSAL    DECIMAL(19,2) NOT NULL,
        EMPTLSTAT INT CHECK(EMPTLSTAT = 1),
        FOREIGN KEY(EMPID,EMPTLSTAT) REFERENCESdbo.tbl_EMPMASTER(EMPID,EMPDSGSTAT));
       
-- Insering Data For TL Only
INSERT INTO  dbo.tbl_EMP_TL
       (EMPID, EMPSAL, EMPTLSTAT)
VALUES (1, 2000, 1);

(1 row(s) affected)

INSERT INTO  dbo.tbl_EMP_TL
       (EMPID, EMPSAL, EMPTLSTAT)
VALUES (2, 1000, 1);

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK__tbl_EMP_TL__1A9D589D". The conflict occurred
in database "TEST", table "dbo.tbl_EMPMASTER".
The statement has been terminated. 

GO
IF OBJECT_ID(N'dbo.tbl_EMP_PRG', N'U') IS NOT NULL
   BEGIN
      DROP TABLE dbo.tbl_EMP_PRG;
   END
GO
CREATE TABLE dbo.tbl_EMP_PRG
       (
         EMPID     INT           NOT NULL PRIMARY KEY,
         EMPSAL    DECIMAL(19,2) NOT NULL,
         EMPTLSTAT INT CHECK(EMPTLSTAT = 2),
         FOREIGN KEY(EMPID,EMPTLSTAT) REFERENCESdbo.tbl_EMPMASTER(EMPID,EMPDSGSTAT)
       );
GO

INSERT INTO  dbo.tbl_EMP_PRG
       (EMPID, EMPSAL, EMPTLSTAT)
VALUES (2, 1000, 2);
      
(1 row(s) affected)

INSERT INTO  dbo.tbl_EMP_PRG
       (EMPID, EMPSAL, EMPTLSTAT)
VALUES (1, 2000, 2);                 
       
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK__tbl_EMP_PRG__260F0B49". The conflict occurred
in database "TEST", table "dbo.tbl_EMPMASTER".
The statement has been terminated. 

Simple XML Tips

Introduction

This article contains the simple XML tips of SQL Server to Represent Data.




Understand the problem
We have two table objects


--- Parent Table
IF OBJECT_ID(N'[dbo].[tbl_EDUCATIONALINSTITUTE]', N'U')IS NOTNULL
   BEGIN
       DROP TABLE [dbo].[tbl_EDUCATIONALINSTITUTE];
   END
GO
CREATE TABLE [dbo].[tbl_EDUCATIONALINSTITUTE]
       (
          IDNO           INT          NOT NULL PRIMARY KEY,
          INSTITUTENAME  VARCHAR(100) NOT NULL
        )
GO

INSERT INTO [dbo].[tbl_EDUCATIONALINSTITUTE]
       (IDNO, INSTITUTENAME)
VALUES (101, 'ABC-Educare'),
       (102, 'SQL Knowledge Bank'),
       (103, 'A to Z Computer Education');
GO

--- Child Table
IF OBJECT_ID(N'[dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]', N'U')ISNOT NULL
   BEGIN
       DROP TABLE [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME];
   END
GO
CREATE TABLE [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]
       (
          CANDIDATEID      INT          NOT NULL IDENTITYPRIMARY KEY,
          IDNO             INT          NOT NULL,
          CANDIDATENAME    VARCHAR(50)  NOT NULL,
          HIGHESTEDUCATION VARCHAR(50)  NOT NULL,
          EXPERIENCEYEAR   INT          NOT NULL
        )
GO

--- Foreign Key Relation

ALTER TABLE [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]
ADD CONSTRAINT FK_tbl_EDUCATIONALINSTITUTE_RESUME_IDNO
FOREIGN KEY(IDNO)
REFERENCES [dbo].[tbl_EDUCATIONALINSTITUTE](IDNO);
GO

INSERT INTO [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]
       (IDNO, CANDIDATENAME, HIGHESTEDUCATION, EXPERIENCEYEAR)
VALUES (101, 'Sukamal Jana', 'MCA', 9),
       (101, 'Anirudha Dey', 'B.Tech', 5),
       (102, 'Joydeep Das', 'MCDBA', 11),
       (102, 'Deepasree Das', 'B.E', 5),
       (103, 'Arabind Sarkar', 'B.E', 5),
       (103, 'Sudip Das', 'M.tech', 15);
GO

What happens After a JOIN
SELECT a.IDNO AS [INSTITUTE ID], a.INSTITUTENAME, b.CANDIDATEID, 
       b.CANDIDATENAME,
       b.HIGHESTEDUCATION, b.EXPERIENCEYEAR
FROM   [dbo].[tbl_EDUCATIONALINSTITUTE] AS a
       INNER JOIN [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME] AS b
                    ON a.IDNO = b.IDNO;

Output:



What Actually We Want

INSTITUTE ID
INSTITUTE NAME
CANDIDATE DETAILS
101
ABC-Educare
XML Script
102
SQL Knowledge Bank
XML Script
103
A to Z Comouter Education
XML Script

So the Institute name is not replicated and the Institute name must appears once. All the candidate within the specified institute must appear in XML Scripts with all details of candidate.

How we can Solve it

SELECT a.IDNO AS [INSTITUTE ID], a.INSTITUTENAME,
       (SELECT CANDIDATEID, CANDIDATENAME, HIGHESTEDUCATION,                             EXPERIENCEYEAR
        FROM   [dbo].[tbl_EDUCATIONALINSTITUTE_RESUME]
        WHERE  IDNO = a.IDNO
        FOR XML RAW('Candidat'), ROOT('DataSet'), ELEMENTS) 
                  AS [CANDIDATE DETAILS]
FROM   [dbo].[tbl_EDUCATIONALINSTITUTE] AS a;



Hope you like it.




TSQL trick only with FOR XML Support

Introduction

In this article we are going to demonstrate a TSQL trick only with FOR XML Support.

Case Study
We have three Table Objects

tbl_CUSTOMER

CUSTID
CUSTNAME
1
Joydeep Das
2
Chandan Bannerjee
3
Soumen Bhowmik

tbl_ITEMDTLS

ITEMCD
ITEMNAME
100
Tooth Paste
101
Tooth Brusg
102
Saving Lotion
103
Saving Brush

Now the customer purchase Items

tbl_SALEDTLS

SALENO
SRLNO
CUSTID
ITEMCD
201
1
1
100
201
2
1
101
201
3
1
102
201
4
1
103
202
1
2
100
202
2
2
101
203
1
3
100

We want a report like this Format

CUSTID
CUSTNAME
ITEM DETAILS
1
Joydeep Das
 Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste
2
Chandan Bannerjee
 Tooth Brusg, Tooth Paste
3
Soumen Bhowmik
 Tooth Paste
1
Joydeep Das
 Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste

How to solve it

-- Table Object Customer
IF OBJECT_ID(N'dbo.tbl_CUSTOMER', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_CUSTOMER];
   END
GO
CREATE TABLE [dbo].[tbl_CUSTOMER]
      (
         CUSTID    INT         NOT NULL IDENTITY PRIMARY KEY,
         CUSTNAME  VARCHAR(50) NOT NULL
      );
GO

-- Insert Records
INSERT INTO [dbo].[tbl_CUSTOMER]
      (CUSTNAME)
VALUES('Joydeep Das'),
      ('Chandan Bannerjee'),
      ('Soumen Bhowmik');                 

-- Table Object Item Details
IF OBJECT_ID(N'dbo.tbl_ITEMDTL', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_ITEMDTL];
   END
GO
CREATE TABLE [dbo].[tbl_ITEMDTL]
   (
     ITEMCD       INT         NOT NULL IDENTITY(100,1) PRIMARY KEY,
     ITEMNAME     VARCHAR(50) NOT NULL
   )
GO

-- Insert Records
INSERT INTO [dbo].[tbl_ITEMDTL] 
    (ITEMNAME)
VALUES('Tooth Paste'),
      ('Tooth Brusg'),
      ('Saving Lotion'),
      ('Saving Brush');
     
-- Table Object Sales Dtls
IF OBJECT_ID(N'dbo.tbl_SALEDTLS', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_SALEDTLS];
   END
GO           
CREATE TABLE [dbo].[tbl_SALEDTLS]
   (
     SALENO   INT   NOT NULL,
     SRLNO    INT   NOT NULL,
     CUSTID   INT   NOT NULL,
     ITEMCD   INT   NOT NULL,
     CONSTRAINT PK_tbl_SALEDTLS PRIMARY KEY
        (
           SALENO ASC,
           SRLNO  ASC
        )
   )           
GO

-- Insert Records
INSERT INTO [dbo].[tbl_SALEDTLS]
      (SALENO, SRLNO, CUSTID, ITEMCD)
VALUES(201, 1, 1, 100),
      (201, 2, 1, 101),
      (201, 3, 1, 102),
      (201, 4, 1, 103),
      (202, 1, 2, 100),
      (202, 2, 2, 101),
      (203, 1, 3, 100);  
     
GO
SELECT * FROM  [dbo].[tbl_CUSTOMER];
SELECT * FROM  [dbo].[tbl_ITEMDTL];
SELECT * FROM  [dbo].[tbl_SALEDTLS]; 

-- Query
SELECT a.CUSTID, a.CUSTNAME,
       STUFF((SELECT ', '+ y.ITEMNAME
              FROM   [dbo].[tbl_SALEDTLS] AS x
                     INNER JOIN [dbo].[tbl_ITEMDTL] AS y
              ON x.ITEMCD = y.ITEMCD
              WHERE  x.CUSTID = a.CUSTID
              ORDER BY ',' + y.ITEMNAME
              FOR XML PATH('')),1,1,'') AS [ITEM DETAILS]
FROM   [dbo].[tbl_CUSTOMER] AS a; 

CUSTID   CUSTNAME                      ITEM DETAILS
1              Joydeep Das                    Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste
2              Chandan Bannerjee       Tooth Brusg, Tooth Paste
3              Soumen Bhowmik           Tooth Paste



Hope you like it.




Integration with the XML Data Type


With the introduction of the XML data type, we wanted to also give FOR XML the ability to generate an instance of XML directly (more precisely, it generates a single row, single column rowset where the cell contains the XML data type instance).

Because of the backwards-compatibility considerations outlined above, we added a new TYPE directive to generate the result as XML. For example,

DECLARE @XMLVAR XML
SET @XMLVAR = (SELECT SALENO,SALEDT FROM TFA_SALEHDR X
                      FOR XML AUTO)

This result is guaranteed to conform to the well-formedness constraints provided by the XML data type. Since the result is an XML data type instance, you can also use XQuery expressions to query and reshape the result. For example, the following expression retrieves the TFA_SALEHDR contact name into a new x element.

SELECT (SELECT SALENO,SALEDT FROM TFA_SALEHDR x 
FOR XML AUTO, TYPE).query(
'<doc>{
   for $c in /x
   return
     <x SALENO="{data($c/@SALENO)}" SALEDT="{data($c/@SALEDT)}"/>
 }</doc>')

returns (first elements 2nd elements shown),

<doc>
  <x SALENO="VSPD/00001/11-12" SALEDT="2011-06-20T00:00:00" />
  <x SALENO="VSPD/00002/11-12" SALEDT="2011-06-20T00:00:00" />
  <x SALENO="VSPD/00003/11-12" SALEDT="2011-06-20T00:00:00" />
  <x SALENO="VSPD/00004/11-12" SALEDT="2011-06-23T00:00:00" />
</doc>

returns the TFA_SALEHDR elements as an XML data type instance, instead of the nvarchar (max) instance that would have been the case without the TYPE directive.
We can read the XML file using the SELECT statement. Following is the XML which we will read using T-SQL:
<doc>
  <x SALENO="VSPD/00001/11-12" SALEDT="2011-06-20T00:00:00" />
  <x SALENO="VSPD/00002/11-12" SALEDT="2011-06-20T00:00:00" />
  <x SALENO="VSPD/00003/11-12" SALEDT="2011-06-20T00:00:00" />
  <x SALENO="VSPD/00004/11-12" SALEDT="2011-06-23T00:00:00" />
</doc>

Following is the T-SQL script which we will be used to read the XML:

SELECT FROM_XML.ID.value('@SALENO','VARCHAR(30)') AS SALENO,
       FROM_XML.ID.value('@SALEDT','DATETIME') AS SALEDT
FROM @XMLVAR.nodes('./X') as FROM_XML(ID)