Foreign Key Support in SQL Server 2016 Memory Optimized Tables


By:   |   Updated: 2017-03-30   |   Comments   |   Related: More > In-Memory OLTP

Problem

The SQL Server In-Memory OLTP feature, also known as Hekaton, is an interesting in-memory processing technology that was introduced in SQL Server 2014 and optimized mainly for the Online Transaction Processing (OLTP) workload. As with any new feature, it came with a number of limitations and restrictions such as the ability to create Foreign Keys on the Memory-Optimized tables. Has this limitation been removed in SQL Server 2016?

Solution

The SQL Server In-Memory OLTP feature, with its exciting in-memory processing technology, enhances SQL Server querying performance in many scenarios, but the limitations and restrictions that came with this feature make it not feasible to use in many cases. In SQL Server 2016, a number of the In-Memory OLTP limitations have been removed, making this feature more useful. One of the limitations removed is the ability to create a Foreign Key on Memory-Optimized tables, which is very important to guarantee data integrity within these tables.

In this tip, we will test the Foreign Key creation using two SQL Server instances, the first one is a SQL Server 2014 instance and the second one is a SQL Server 2016 instance, both containing the same MSSQLTipsDemo testing database. Let's prepare the MSSQLTipsDemo database in both instances to host the memory optimized tables, by adding a new filegroup that contains MEMORY_OPTIMIZED_DATA, adding a new database data file on that filegroup and finally enabling the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT as shown in the below script:

USE MSSQLTipsDemo 
GO
ALTER DATABASE MSSQLTipsDemo ADD FILEGROUP MemoryOpt_FG CONTAINS MEMORY_OPTIMIZED_DATA   
ALTER DATABASE MSSQLTipsDemo ADD FILE (name='MemoryOptDataDF', filename='D:\Data\MemoryOptDataDF') TO FILEGROUP MemoryOpt_FG   
ALTER DATABASE MSSQLTipsDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO

Now the MSSQLTipsDemo databases in both instances are ready to host the memory optimized tables. We will start by creating the EmployeeDep_MemOptTable as a simple memory optimized table that will act as the parent table on both instances:

USE MSSQLTipsDemo 
GO

CREATE TABLE [EmployeeDep_MemOptTable]
(
 [DepID] INT IDENTITY(1,1) NOT NULL  CONSTRAINT PK_EmployeeDep_MemOptTable_Depid PRIMARY KEY NONCLUSTERED HASH (DepID) WITH (BUCKET_COUNT = 10000),
 [Dep_Name] nvarchar(10) NULL
 )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

Then trying to create the child Employee_MemOptTable memory optimized table that contains the Dep_ID column as a foreign key from the previous table. If we try to run the below CREATE TABLE script on the SQL Server 2014 instance:

USE [MSSQLTipsDemo]
GO
SELECT @@VERSION AS SQLServerVersion
GO
CREATE TABLE [Employee_MemOptTable]
(
 [ID] INT IDENTITY(1,1) NOT NULL  CONSTRAINT PK_Employee_MemOptTable_id PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT = 10000),
 [First_Name] nvarchar(10) NULL,
 [FLast_Name] nvarchar(10) NULL,
 [Dep_ID] INT  CONSTRAINT fk_Employee_MemOptTable_DepID FOREIGN KEY REFERENCES EmployeeDep_MemOptTable(DepID),
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

The table creation will fail with the below error message, indicating that the Foreign Key is not supported with memory optimized tables:

table creation will fail indicating that the Foreign Key is not supported with the memory optimized tables

Trying the same CREATE TABLE script on the SQL Server 2016 instance, the command will execute successfully and the table will be created with no error, as the Foreign Key is supported now in SQL Server 2016 with memory optimized tables:

Foreign Key are supported in SQL Server 2016 with the memory optimized tables

We can also check the Foreign Key functionality by inserting two new records to the EmployeeDep_MemOptTable parent table:

USE [MSSQLTipsDemo]
GO
INSERT INTO [dbo].[EmployeeDep_MemOptTable]
           ([Dep_Name])
     VALUES
           ('IT'), ('HR')
GO

Then trying to insert a new record to the Employee_MemOptTable child table with Dep_ID value not in the parent table:

USE [MSSQLTipsDemo]
GO

INSERT INTO [dbo].[Employee_MemOptTable]
           ([First_Name]
           ,[FLast_Name]
           ,[Dep_ID])
     VALUES
           ('AA','BB',3)
GO

The INSERT statement will fail stating the inserted value conflicts with the Foreign Key constraint as follows:

The INSERT statement will fail showing that the inserted value conflicted with the Foreign Key constraint

Let's insert the record again with a valid Dep_ID value:

USE [MSSQLTipsDemo]
GO

INSERT INTO [dbo].[Employee_MemOptTable]
           ([First_Name]
           ,[FLast_Name]
           ,[Dep_ID])
     VALUES
           ('AA','BB',1)
GO

The record will be inserted successfully:

row

What if we try to create a standard disk based table with a Foreign Key that references a Memory optimized table? Let's try to create the Employee_PhysicalTable table with the same schema as the Employee_MemOptTable table, but this time with a normal disk based table, and again with a Foreign Key that referenced the Dep_ID column to the EmployeeDep_MemOptTable parent Memory optimized table:

USE [MSSQLTipsDemo]
CREATE TABLE [Employee_PhysicalTable]
(
 [ID] INT IDENTITY(1,1) NOT NULL  CONSTRAINT PK_Employee_PhysicalTable_id PRIMARY KEY NONCLUSTERED ,
 [First_Name] nvarchar(10) NULL,
 [FLast_Name] nvarchar(10) NULL,
 [Dep_ID] INT  CONSTRAINT fk_Employee_PhysicalTable_DepID FOREIGN KEY REFERENCES EmployeeDep_MemOptTable(DepID)
)
GO

The result is unexpected, showing that the Foreign Key relationship between the memory optimized and non-memory optimized table is not supported in SQL Server 2016 as shown in the error message below:

Foreign Key relationship between the memory optimized and non-memory optimized table is not supported in SQL Server 2016
Next Steps


Last Updated: 2017-03-30


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools