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