Sep 24, 2013

The feature 'foreign key constraints' is not supported with memory optimized tables

If we will try add foreign key constraints between two memory optimized tables in sql server 2014 by following script:

CREATE TABLEtblEmp(
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(200),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)

Go

CREATE TABLEtblEmpDetial(
     EmpDetialID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (EmpDetialID)
     WITH (BUCKET_COUNT=1024),
     ntEmpID BIGINT REFERENCEStblEmp(ntEmpID),
     vcDetial VARCHAR(6000)
)
WITH(MEMORY_OPTIMIZED =ON)

We may get error message something like this:

Msg 10770, Level 16, State 11, Line 14
The feature 'foreign key constraints' is not supported with memory optimized tables.

Cause: We cannot add foreign key constraint between two memory optimized tables in sql server 2014.

Solution: We can enforce this constraint by creating the natively complied stored procedures. That is creating the tables without foreign key constraint and we will have to insert, delete or update the records of the table by using the stored procedures. For example:

--To Insert
CREATE PROCEDURE[dbo].[uspAddEmployeeDetails](
     @EmpDetialID BIGINT,
     @ntEmpID BIGINT,
     @vcDetial VARCHAR(6000)
      
)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATIONLEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)   
     DECLARE @ntFkEmpID ASBIGINT

     BEGIN TRY

          SELECT @ntFkEmpID =ntEmpID FROM dbo.tblEmp WHERE ntEmpID =@ntEmpID

          IF @ntFkEmpID IS NULL BEGIN
              SET @ntFkEmpID = 0
              SET @ntFkEmpID = 1 / @ntFkEmpID
          END

          INSERT INTO dbo.tblEmpDetial(EmpDetialID,ntEmpID,vcDetial) VALUES(@EmpDetialID,@ntEmpID,@vcDetial)

          SELECT 'Success' AS vcStatus

     END TRY
     BEGIN CATCH
         
          SELECT 'The INSERT statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database "MSSQL2014", table "dbo.tblEmp", column ntEmpID' AS vcStatus

     END CATCH

END

--To Delete

CREATE PROCEDURE[dbo].[uspRemoveEmployee](
     @ntEmpID BIGINT,
     @btCaseCadeDelete BIT = 0
    
)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATIONLEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)   
     DECLARE @ntFkEmpID ASBIGINT

     BEGIN TRY

          IF @btCaseCadeDelete =0 BEGIN
             
              SELECT @ntFkEmpID =ntEmpID FROM dbo.tblEmpDetial WHERE ntEmpID =@ntEmpID

              IF @ntFkEmpID IS NOT NULL BEGIN

                   SET @ntFkEmpID = 0
                   SET @ntFkEmpID =@ntFkEmpID / @ntFkEmpID

              END

          END ELSE BEGIN

              DELETE FROM dbo.tblEmpDetial WHEREntEmpID = @ntEmpID
          END

          DELETE FROM dbo.tblEmp WHERE ntEmpID = @ntEmpID

          SELECT 'Success' AS vcStatus

     END TRY
     BEGIN CATCH
         
          SELECT 'The DELETE statement conflicted with the REFERENCE constraint. The conflict occurred in database "MSSQL2014", table "dbo.tblEmpDetial1", column "ntEmpID"' AS vcStatus
         
     END CATCH


END

No comments:

Post a Comment