When InMemory OLTP was introduced in SQL Server 2014, there were a number of limitations that restricted customers from effectively using this capability. One of the most commonly asked features was the ability to create Foreign Keys. Initially, it seemed like this feature was already available, but upon further experimentation and reading the documentation, it became clear that this was a missing capability.

With the release of SQL Server 2016, I discovered that this feature was added subtly. I was excited to test it on my local CTP3.2 version, as it would also work once the RTM (Release to Manufacturing) version is available. The addition of Foreign Keys is significant because data integrity is of prime importance when working with databases.

Let’s start by creating our InMemory OLTP database and two tables with constraints, and then add some data to them:

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]
GO

-- Create the table structures
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

-- Insert values to test the Foreign Key constraint
USE InMem_OLTP
GO

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

As expected, we receive a foreign key violation error:

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 many limitations present in prior editions. In future articles, we will explore each of these capabilities in more detail.