SQL SERVER 2016 – InMemory OLTP support for Foreign Key

When InMemory OLTP was introduced in SQL Server 2014, there were a number of limitations that restricted customers to effectively use the capability. One of the most commonly asked capability was around the ability to create Foreign Keys. On first look, I thought this was always available. But while experimenting and reading the documentation figured out this was the most asked and missing capability.

With SQL Server 2016, I found this feature was added subtly and the first thing I wanted to do was to check if it works on my local CTP3.2 versions. Yes, this would also work once the RTM happens but I was excited because a lot of times, people want to use Foreign Keys because date integrity is of prime importance when working with databases.

Here, I am going to start creating our InMemory OLTP database and create two tables with constraints and add some data to it.

USE MASTER
GO
DROP DATABASE IF EXISTS [InMem_OLTP] GO
-- Create the database
CREATE DATABASE [InMem_OLTP] ON  PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',size = 30MB)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB)
GO
-- Add Inmemory Filegroup
USE InMem_OLTP
GO
ALTER DATABASE InMem_OLTP
ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE InMem_OLTP
ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt')
TO FILEGROUP [InMem_OLTP_InMemory]

We will create two tables as Products and Sales and create respective constraints to them.

-- Let us create the table srtuctures
USE InMem_OLTP
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY NONCLUSTERED HASH (ProductID) WITH (BUCKET_COUNT = 10000),
ProductName VARCHAR(25)
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY NONCLUSTERED HASH (SalesID) WITH (BUCKET_COUNT = 10000),
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO


Next is to test if the Foreign Key has taken effect. Let us insert some values into our database tables for the same.

-- Insert Values to see
INSERT INTO Products VALUES (1, 'SQLAuthority');
-- Success
INSERT INTO ProductSales VALUES (1, 1, 'Pinal');
-- Error
INSERT INTO ProductSales VALUES (2, 2, 'Dave');


As guessed right, we get the foreign key violation error as:

Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint “fk_productSales_pid”. The conflict occurred in database “InMem_OLTP”, table “dbo.Products”, column ‘ProductID’.
The statement has been terminated.

As you can see, SQL Server 2016 has introduced capabilities that remove a lot of limitations that was available in prior editions. We will explore each of these in future too.

Reference: Pinal Dave (https://blog.sqlauthority.com)