SQL SERVER – Error Msg 10778, Level 16 with InMemory OLTP

SQL
1 Comment

SQL Server 2016 brought several innovations. One of the most anticipated feature additions was the ability to define foreign keys with InMemory tables. I wrote about this feature earlier on blog at: SQL SERVER 2016 – InMemory OLTP support for Foreign Key.

Though this was a great addition, I always felt there were restrictions still to know and understand. The twist to the previous blog was to create a Foreign key on a standard disk based table. I went about using the below script for the same.

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,
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
 )
GO

I was pleasantly surprised that an error message popped up. I am showing the same below:

Solarwinds

SQL SERVER - Error Msg 10778, Level 16 with InMemory OLTP inmemory-disk-tables-foreign-key-error-01-800x273

Msg 10778, Level 16, State 0, Line 1
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.

This was a great learning that was worth a share via blogs. Though the foreign key addition was lesser known to many, even more lesser known is the fact that it cannot be across disk based tables and in-memory tables. Do let me know if you were aware of this?

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

Solarwinds
, , ,
Previous Post
SQL SERVER – Understanding JSON NULL Value Using STRICT Keyword
Next Post
SQL SERVER – Error: 566, Severity: 21 – An Error Occurred While Writing an Audit Trace. SQL Server is Shutting Down

Related Posts

1 Comment. Leave new

Leave a Reply

Menu