SQL SERVER – 2016 Error Reasons: Msg 10778, Level 16 Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported

When I was writing the blog SQL SERVER 2016 – InMemory OLTP support for Foreign Key, it felt like a great addition to SQL Server. I felt there must be something that I might be missing as the obvious. I was not wrong in that feeling too. One of my blog readers pinged to say they were getting a strange error that Foreign Key was not supported. This is how I figured out what was going wrong.

Pinal: Hi buddy.

Reader: Thanks for the ping.

Pinal: Not an issue. It is my pleasure.

Reader: As you know, based on your last week blog. I was experimenting something.

Pinal: Go on, anything related to this blog is something I am all ears.

Reader: I am getting an error.

Pinal: Which Blog are we talking?

Reader: It is the OLTP support for Foreign keys.

Pinal: Oh that one. That was a simple blog. What is the error?

Reader: It say it is not supported.

Pinal: Are you sure you are on a SQL Server 2016 version when you are trying that? It wouldn’t work on a SQL Server 2014 edition.

Reader: I am on the latest SQL Server 2016 version. But I was experimenting and got this error.

Pinal: What sort of experiment? What error are we talking?

Reader: The error states: “Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.”

Pinal: No wonder. It is quite possible. Now I get it. You didn’t run the script as-is but changed one of them to Disk based tables.

The reader had changed the blog SQL SERVER 2016 – InMemory OLTP support for Foreign Key, and edited the portion of creation of table. It was as below:

CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
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

As you can see the Products table was made as normal Disk based table while the transactions table was made as InMemory OLTP table. I am sure this is the strategy for most of the users wherein they will be creating normal tables for their masters and using the InMemory capability for the transactions. At the moment we will receive the error:

Msg 10778, Level 16, State 0, Line 3
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint or index. See previous errors.

I am sure the SQL Server product team is looking into this and maybe in subsequent versions there will be a provision for the same. But how important are these capabilities in your opinion? Do you want relationship management from InMemory and Disk based tables? Do let me know via the comments.

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