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