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,
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),
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
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)
Hello The Doc,
Please I am trying to migrate ssis packages from 2012 t0 2008….this is a downgrade….Moving from 2008 to 2912 is straightforward.. 2012 to 2008 is a problem…any help?
>>Do you want relationship management from InMemory and Disk based tables? Do let me know via the comments.
It is obligatory. Without relationships between InMemory and OnDisk tables your DB is broken down on two different irrelevant pieces.
As usualy for MS – very loud drums on entry – very loud fart on exit.
I am totally disappointed to see how MS not incorportaed this basic need. whats the point to migrate to sql 2016 where they have not incrportaed basic stuff. Majority people like to have disk base & memory base table interactions but it is not there in sql 2016.
I used sql 2014 and found it is useless as lots of limitations but also disappointed to see sql 2016 as well.