How to delete cascade on Memory-Optimized tables?

I have 2 tables: master->detail, how can I implement 'delete cascade' like referential integrity on delete cascade for disk-based tables?

What way to use?

Solution

Unfortunately that doesn't seem to be supported yet, so you have to do it by yourself. Following article describes pretty well how to mimic the cascade delete:

Workaround for lack of support for constraints on SQL Server Memory-Optimized Tables

Since link only answers are not welcome here(links can die), here's the content(thanks to the original author Daniel Farina):

One of the reasons that might keep you from migrating tables to In-Memory OLTP is the lack of support for constraints and default values for Memory-Optimized Tables. With the help of stored procedures, views, triggers and a bit of ingenuity we can overcome this limitation.

Review about the types of constraints and their support for Memory-Optimized Tables.

Types of Constraints

Constraint	Supported	Description
NOT NUL	Yes	Specifies that the column does not accept NULL values.
CHECK	No	Enforces domain integrity by limiting the values that can be put in a column.
UNIQUE	No	Enforce the uniqueness of the values in a set of columns.
PRIMARY KEY	Yes	Identify the column or set of columns that have values that uniquely identify a row in a table.
FOREIGN KEY	No	Identify and enforce the relationships between tables.
DEFAULT	No	Provides a default value to a column when the INSERT statement does not provide a specific value.

So, we need a work around for FOREIGN KEY, CHECK, UNIQUE and DEFAULT constraints.

Way to resolve the constraint limitations

At first glance, the trivial solution is to use wrapper views and create triggers for DML operations, but there is an issue with that. We must ensure that nobody inserts records that violate the constraints on the base tables.

The solution is to create a user to act as a proxy, set permissions and create the triggers with the EXECUTE AS USER clause.

Sample implementation

A simple design to show how to set up constraints, here is a diagram of the on disk tables.

From the image above we can see the following relations defined:

Table OnDisk.OrderHeader establishes a FOREIGN KEY constraint on column CustomerID that references column CustomerID on OnDisk.Customers table. The Column CustomerID is nullable because I created the foreign key with the ON DELETE SET NULL clause.
Table OnDisk.OrderDetail has two foreign keys. One is on ProductID which references table OnDisk.Products and the other is on OrderID to reference OnDisk.OrderHeader table. I created the last foreign key with ON DELETE CASCADE clause, so if a row is deleted from OnDisk.OrderHeader table, all rows of OnDisk.OrderDetail which are referenced will be deleted.

Constraints

Table OnDisk.Products has a CHECK constraint to verify that unit price is bigger than unit cost. Table OnDisk.Customers has a UNIQUE constraint on CustomerName and CustomerAddress, so no customers with the same name and address can be in the table.

Default values

Table OnDisk.OrderHeader has a default value of GETDATE() for column OrderDate.

Scripts to simulate constraints for memory optimized tables

I will guide you step by step on how to implement the constraints with the following scripts which I will explain next.

1. Create sample database

First we need to create a sample database with a Memory-Optimized Filegroup.

CREATE DATABASE TestDB
ON PRIMARY
 (NAME = TestDB_file1,
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_1.mdf',
     SIZE = 100MB,     
     FILEGROWTH = 10%),
FILEGROUP TestDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
 ( NAME = TestDB_MemoryOptimized,
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_MemoryOptimized')
LOG ON
 ( NAME = TestDB_log_file1,
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_1.ldf',
     SIZE = 100MB,     
     FILEGROWTH = 10%)
GO

2. Create schemas for On Disk and In Memory tables

I decided to use two different schemas. One for Disk based tables and the other for Memory optimized tables.

USE [TestDB]
GO
CREATE SCHEMA OnDisk
GO
CREATE SCHEMA InMemory
GO

3. Create On Disk tables

The next script creates the schema and disk based tables that we will use as reference. These are only used as reference so we can mimic the behavior.

USE TestDB;
GO

/* First we drop the tables if they exists*/
IF OBJECT_ID('OnDisk.OrderDetail','U') IS NOT NULL
BEGIN
 DROP TABLE OnDisk.OrderDetail
END
GO
IF OBJECT_ID('OnDisk.OrderHeader','U') IS NOT NULL
BEGIN
 DROP TABLE OnDisk.OrderHeader
END
GO
IF OBJECT_ID('OnDisk.Customers','U') IS NOT NULL
BEGIN
 DROP TABLE OnDisk.Customers
END
GO
IF OBJECT_ID('OnDisk.Products','U') IS NOT NULL
BEGIN
 DROP TABLE OnDisk.Products
END
GO
/*------------------------------------------------------------------------*/
/* Now we create the tables*/
CREATE TABLE OnDisk.Products
(
  ProductID   INT IDENTITY (1, 1) NOT NULL,
  Description  NVARCHAR (50) NOT NULL,
  UnitCost    MONEY NULL,
  UnitPrice   MONEY NULL,
  UnitsInStock  INT NULL,
  Active     BIT NULL,
  CHECK ( (UnitPrice > UnitCost)),
  PRIMARY KEY CLUSTERED (ProductID)
)
CREATE TABLE OnDisk.Customers
(
  CustomerID    INT IDENTITY (1, 1) NOT NULL,
  CustomerName   NVARCHAR (50) NOT NULL,
  CustomerAddress  NVARCHAR (50) NULL,
  PRIMARY KEY CLUSTERED (CustomerID),
  CONSTRAINT U_OnDisk_Customersg_1 UNIQUE NONCLUSTERED (CustomerName, CustomerAddress)
)
CREATE TABLE OnDisk.OrderHeader
(
  OrderID   INT IDENTITY (1, 1) NOT NULL,
  OrderDate  DATE NOT NULL DEFAULT (GETDATE ()),
  CustomerID  INT NULL,
  TotalDue   MONEY NOT NULL,
  PRIMARY KEY CLUSTERED (OrderID),
  FOREIGN KEY (CustomerID)
   REFERENCES OnDisk.Customers (CustomerID) ON DELETE SET NULL
)
CREATE TABLE OnDisk.OrderDetail
(
  OrderID     INT NOT NULL,
  OrderDetailID  INT IDENTITY (1, 1) NOT NULL,
  ProductID    INT NOT NULL,
  Quantity    INT NOT NULL,
  PRIMARY KEY CLUSTERED (OrderID, OrderDetailID),
  FOREIGN KEY (ProductID) 
  REFERENCES OnDisk.Products (ProductID),
  FOREIGN KEY (OrderID) 
  REFERENCES OnDisk.OrderHeader (OrderID) ON DELETE CASCADE
)
GO

4. Create database user

This script will create a new user "InMemoryUsr" without a login and with default schema "InMemory".

USE TestDB
GO
CREATE USER InMemoryUsr WITHOUT LOGIN WITH DEFAULT_SCHEMA = InMemory
GO

5. Create Memory-Optimized tables

Now we create the Memory-Optimized tables.

USE TestDB;
GO
IF OBJECT_ID('InMemory.tblProducts','U') IS NOT NULL
BEGIN
 DROP TABLE InMemory.tblProducts
END
GO
CREATE TABLE InMemory.tblProducts
(
  ProductID   INT NOT NULL,
  Description  NVARCHAR (50) NOT NULL,
  UnitCost    MONEY NULL,
  UnitPrice   MONEY NULL,
  UnitsInStock  INT NULL,
  Active     BIT NULL,  
  PRIMARY KEY NONCLUSTERED HASH (ProductID) 
    WITH (BUCKET_COUNT = 1024) 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
IF OBJECT_ID('InMemory.tblCustomers','U') IS NOT NULL
BEGIN
 DROP TABLE InMemory.tblCustomers
END
GO
CREATE TABLE InMemory.tblCustomers
(
  CustomerID    INT NOT NULL,
  CustomerName   NVARCHAR (50) COLLATE Latin1_General_100_BIN2 NOT NULL,
  CustomerAddress  NVARCHAR (50) COLLATE Latin1_General_100_BIN2 NOT NULL,
  ChkSum    INT NOT NULL
  PRIMARY KEY NONCLUSTERED HASH (CustomerID) WITH (BUCKET_COUNT = 1024),
  INDEX IX_InMemory_TblCustomers_CustomerName_CustomerAddress HASH (ChkSum) 
    WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
IF OBJECT_ID('InMemory.tblOrderHeader','U') IS NOT NULL
BEGIN
 DROP TABLE InMemory.tblOrderHeader
END
GO

CREATE TABLE InMemory.tblOrderHeader
(
  OrderID   INT NOT NULL,
  OrderDate  DATE NOT NULL,
  CustomerID  INT NULL,
  TotalDue   MONEY NOT NULL,
  PRIMARY KEY NONCLUSTERED HASH (OrderID) 
    WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
IF OBJECT_ID('InMemory.tblOrderDetail','U') IS NOT NULL
BEGIN
 DROP TABLE InMemory.tblOrderDetail
END
GO

CREATE TABLE InMemory.tblOrderDetail
(
  OrderID     INT NOT NULL,
  OrderDetailID  INT NOT NULL,
  ProductID    INT NOT NULL,
  Quantity    INT NOT NULL,
  PRIMARY KEY NONCLUSTERED HASH (OrderID, OrderDetailID) 
    WITH (BUCKET_COUNT = 1024) ,
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Note that the table InMemory.tblCustomers has an additional column called "ChkSum". That is in order to set up the UNIQUE Constraint on CustomerName and CustomerAddress. At first sight you may think about performing a string comparison, but that would be very inefficient. So I decided to create the "ChkSum" column and an index to keep the checksum of columns CustomerName and CustomerAddress to speed up comparisons. To understand this, think about how a UNIQUE constraint is implemented by the SQL Server engine. The UNIQUE constraint is much like a UNIQUE INDEX which is not supported on Memory-Optimized Tables. Also the CHECKSUM() function can be used to build a hash index.

6. Create sequences to mimic IDENTITY columns

Because of the lack of support for IDENTITY columns for Memory-Optimized Tables, we need to create sequence objects that will provide us the same functionality when we use them inside a trigger.

USE [TestDB]
GO
IF OBJECT_ID('InMemory.SO_Products_ProductID', 'SO') IS NOT NULL 
  BEGIN
    DROP SEQUENCE InMemory.SO_Products_ProductID
  END
GO
CREATE SEQUENCE InMemory.SO_Products_ProductID 
START WITH 1
INCREMENT BY 1
GO
IF OBJECT_ID('InMemory.SO_Customers_CustomerID', 'SO') IS NOT NULL 
  BEGIN
    DROP SEQUENCE InMemory.SO_Customers_CustomerID
  END
GO
CREATE SEQUENCE InMemory.SO_Customers_CustomerID 
START WITH 1
INCREMENT BY 1
GO
IF OBJECT_ID('InMemory.SO_OrderHeader_OrderID', 'SO') IS NOT NULL 
  BEGIN
    DROP SEQUENCE InMemory.SO_OrderHeader_OrderID
  END
GO
CREATE SEQUENCE InMemory.SO_OrderHeader_OrderID 
START WITH 1
INCREMENT BY 1
GO
IF OBJECT_ID('InMemory.SO_OrderDetail_OrderDetailID', 'SO') IS NOT NULL 
  BEGIN
    DROP SEQUENCE InMemory.SO_OrderDetail_OrderDetailID
  END
GO
CREATE SEQUENCE InMemory.SO_OrderDetail_OrderDetailID 
START WITH 1
INCREMENT BY 1
GO

7. Create views

We are ready to create the views that will act as a wrapper to the base tables.

These are very simple views with the addition of the WITH SNAPSHOT hint to avoid cross-container transactions when accessing Memory-Optimized tables. Also note that the view InMemory.Customers does not include column "ChkSum". That column is for our internal use only.

USE TestDB
GO
IF OBJECT_ID('InMemory.Customers', 'V') IS NOT NULL 
  BEGIN
    DROP VIEW InMemory.Customers
  END
GO
CREATE VIEW InMemory.Customers
 WITH SCHEMABINDING
AS
  SELECT CustomerID ,
      CustomerName ,
      CustomerAddress
  FROM  InMemory.tblCustomers WITH (SNAPSHOT)
GO

USE TestDB
GO
IF OBJECT_ID('InMemory.Products', 'V') IS NOT NULL 
  BEGIN
    DROP VIEW InMemory.Products
  END
GO
CREATE VIEW InMemory.Products
 WITH SCHEMABINDING
AS
  SELECT ProductID ,
      Description ,
      UnitCost ,
      UnitPrice ,
      UnitsInStock ,
      Active
  FROM  InMemory.tblProducts WITH (SNAPSHOT)
GO

USE TestDB
GO
IF OBJECT_ID('InMemory.OrderHeader', 'V') IS NOT NULL 
  BEGIN
    DROP VIEW InMemory.OrderHeader
  END
GO
CREATE VIEW InMemory.OrderHeader
 WITH SCHEMABINDING
AS
  SELECT OrderID ,
      OrderDate ,
      CustomerID ,
      TotalDue
  FROM  InMemory.tblOrderHeader WITH (SNAPSHOT)
GO

USE TestDB
GO
IF OBJECT_ID('InMemory.OrderDetail', 'V') IS NOT NULL 
  BEGIN
    DROP VIEW InMemory.OrderDetail
  END
GO
CREATE VIEW InMemory.OrderDetail
 WITH SCHEMABINDING
AS
  SELECT OrderID ,
      OrderDetailID ,
      ProductID ,
      Quantity
  FROM  InMemory.tblOrderDetail WITH (SNAPSHOT)
GO

8. Create INSTEAD OF Triggers for View InMemory.Products

Here we will simulate a CHECK constraint on the INSTEAD OF INSERT and INSTEAD OF UPDATE triggers to verify that unit price is bigger than unit cost. To do this, we only need to check for the existence of records that do not accomplish the condition above and if so, throw an exception.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Products_Insert', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Products_Insert
  END
GO
CREATE TRIGGER InMemory.TR_Products_Insert ON InMemory.Products  
 WITH EXECUTE AS 'InMemoryUsr' 
 INSTEAD OF INSERT
AS
 SET NOCOUNT ON
 --CHECK ( (UnitPrice > UnitCost))  
 IF EXISTS ( SELECT 0
        FROM  INSERTED
        WHERE  UnitPrice < UnitCost ) 
    BEGIN
      ;THROW 50001, 'Violation of CHECK Constraint! (UnitPrice > UnitCost)!', 1
    END
  INSERT INTO InMemory.tblProducts WITH (SNAPSHOT)
      ( ProductID, 
       Description ,
       UnitCost ,
       UnitPrice ,
       UnitsInStock ,
       Active
      )
      SELECT NEXT VALUE FOR
          InMemory.SO_Products_ProductID ,
          Description ,
          UnitCost ,
          UnitPrice ,
          UnitsInStock ,
          Active
      FROM  INSERTED 
 
 GO

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_tblProducts_Update', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_tblProducts_Update
  END
GO
CREATE TRIGGER InMemory.TR_tblProducts_Update ON InMemory.Products
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF UPDATE
AS
 SET NOCOUNT ON
  
 --CHECK ( (UnitPrice > UnitCost))   
  IF EXISTS ( SELECT 0
        FROM  INSERTED
        WHERE  UnitPrice < UnitCost) 
    BEGIN
      ;THROW 50001, 'Violation of CHECK Constraint! (UnitPrice > UnitCost)', 1
    END
 UPDATE InMemory.tblProducts WITH (SNAPSHOT)
  SET  Description = I.DESCRIPTION,
    UnitCost = I.UnitCost,
    UnitPrice = I.UnitPrice,
    UnitsInStock = I.UnitsInStock,
    Active = I.Active
 FROM INSERTED I 
 INNER JOIN InMemory.tblProducts P WITH (SNAPSHOT)
  ON I.ProductID = P.ProductID
 GO

In the INSTEAD OF DELETE trigger we must validate that the simulated foreign key on table InMemory.tblOrderDetail won't be violated. So we need to check the records to be deleted on table InMemory.tblProducts are not referenced by table InMemory.tblOrderDetail.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Products_Delete', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Products_Delete
  END
GO
CREATE TRIGGER InMemory.TR_Products_Delete ON InMemory.Products
 WITH EXECUTE AS 'InMemoryUsr'
 INSTEAD OF DELETE
AS
 SET NOCOUNT ON
 
 -- TABLE InDisk.OrderDetail
 -- FOREIGN KEY (ProductID) REFERENCES OnDisk.Products (ProductID) 
  IF EXISTS ( SELECT 0
        FROM  DELETED D
    INNER JOIN InMemory.tblOrderDetail OD WITH (SNAPSHOT)
     ON D.ProductID = OD.ProductID
        ) 
    BEGIN
      ;THROW 50001, 'Violation of FOREIGN KEY Constraint! Table InMemory.OrderDetail, Column (ProductID)', 1
    END
    
 DELETE InMemory.tblProducts WITH (SNAPSHOT)
  FROM InMemory.tblProducts P WITH (SNAPSHOT)
  INNER JOIN DELETED D
  ON p.ProductID = D.ProductID
GO

9. Create INSTEAD OF Triggers for View InMemory.Customers

In the following scripts we are going to emulate the UNIQUE constraint for table InMemory.tblCustomers. As I mentioned before, we are using the CHECKSUM() function to implement the UNIQUE constraint. We must check that data to be inserted is different then existing data in the table and that data is different itself. This is very important, because if you omit the last check, you will insert invalid data without notice.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Customers_Insert', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Customers_Insert
  END
GO
CREATE TRIGGER InMemory.TR_Customers_Insert ON InMemory.Customers
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF INSERT  
AS
 SET NOCOUNT ON
 --CONSTRAINT U_OnDisk_Customersg_1 UNIQUE NONCLUSTERED (CustomerName, CustomerAddress)
  IF EXISTS (
    -- Check if rows to be inserted are consistent with CHECK constraint by themselves
    SELECT 0
        FROM  INSERTED I
    GROUP BY CHECKSUM(I.CustomerName, I.CustomerAddress) 
    HAVING COUNT(0) > 1
    UNION ALL
        
       -- Check if rows to be inserted are consistent with UNIQUE constraint with existing data
    SELECT 0
        FROM  INSERTED I
    INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
      ON  C.ChkSum = CHECKSUM(I.CustomerName, I.CustomerAddress)
    ) 
    BEGIN
      ;THROW 50001, 'Violation of UNIQUE Constraint! (CustomerName, CustomerAddress)', 1
    END
  INSERT INTO InMemory.tblCustomers WITH (SNAPSHOT)
      ( CustomerID ,
       CustomerName ,
       CustomerAddress,
    chksum
      )
  SELECT  NEXT VALUE FOR InMemory.SO_Customers_CustomerID ,
      CustomerName ,
      CustomerAddress,
   CHECKSUM(CustomerName, CustomerAddress)
 FROM INSERTED
 GO

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Customers_Update', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Customers_Update
  END
GO
CREATE TRIGGER InMemory.TR_Customers_Update ON InMemory.Customers
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF UPDATE
AS
 --CONSTRAINT U_OnDisk_Customersg_1 UNIQUE NONCLUSTERED (CustomerName, CustomerAddress)
  IF EXISTS (
    -- Check if rows to be inserted are consistent with UNIQUE constraint by themselves
    SELECT 0
        FROM  INSERTED I
    GROUP BY CHECKSUM(I.CustomerName, I.CustomerAddress)
    HAVING COUNT(0) > 1
    UNION ALL
        
       -- Check if rows to be inserted are consistent with UNIQUE constraint with existing data
    SELECT 0
        FROM  INSERTED I
    INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
      ON  C.ChkSum = CHECKSUM(I.CustomerName, I.CustomerAddress)
    ) 
    BEGIN
      ;THROW 50001, 'Violation of UNIQUE Constraint! (CustomerName, CustomerAddress)', 1
    END
  UPDATE InMemory.tblCustomers WITH (SNAPSHOT)
  SET  CustomerName = I.CustomerName,
       CustomerAddress = I.CustomerAddress,
    ChkSum = CHECKSUM(I.CustomerName, I.CustomerAddress)
 FROM INSERTED I
 INNER JOIN InMemory.tblCustomers C WITH (SNAPSHOT)
  ON I.CustomerID = C.CustomerID
 GO

Inside the INSTEAD OF DELETE trigger we will set the CustomerID column of InMemory.tblOrderHeader table to NULL following the foreign key definition.

USE TestDB
GO
IF OBJECT_ID('InMemory.TR_Customers_Delete', 'TR') IS NOT NULL 
  BEGIN
    DROP TRIGGER InMemory.TR_Customers_Delete
  END
GO
CREATE TRIGGER InMemory.TR_Customers_Delete ON InMemory.Customers
 WITH EXECUTE AS 'InMemoryUsr'
  INSTEAD OF DELETE
AS
 SET NOCOUNT ON
 -- Enforces FOREIGN KEY of OrderHeader 
 --FOREIGN KEY (CustomerID)
 --REFERENCES OnDisk.Customers (CustomerID) ON DELETE SET NULL
 UPDATE InMemory.tblOrderHeader WITH (SNAPSHOT)
  SET CustomerID = NULL
 FROM InMemory.tblOrderHeader OH WITH (SNAPSHOT)
 INNER JOIN DELETED D
  ON OH.CustomerID = D.CustomerID 
  DELETE InMemory.tblCustomers WITH (SNAPSHOT)
  FROM InMemory.tblCustomers C WITH (SNAPSHOT)
  INNER JOIN DELETED D
  ON C.CustomerID = D.CustomerID 
 GO

10. Create INSTEAD OF Triggers for View InMemory.OrderHeader

To emulate the foreign key, we need to check that the CustomerID value being inserted exists in InMemory.tblCustomers table. And to implement the default value of column OrderDate we can use the ISNULL() function to check if a value has been inserted, and if not, we issue a call to GETDATE() function. In addition, in the INSTEAD OF DELETE trigger we must delete all referenced records of InMemory.tblOrderDetail table to enforce cascade deletion.

Since stackoverflow has a body limit i need to stop here. You can look at the original article to find remaining details if you need them.