How to fix Error 10719 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 10719 in SQL Server 2025

Why This Error Happens

This error occurs because SQL Server’s In-Memory OLTP engine enforces strict durability consistency rules between related memory-optimized tables. A table with DURABILITY = SCHEMA_AND_DATA guarantees that both schema and data persist across restarts and survive a crash, but a table with DURABILITY = SCHEMA_ONLY only retains its structure, losing all data on restart. Allowing a foreign key from a durable table to a non-durable table would create an unresolvable referential integrity gap any time the server restarts and the SCHEMA_ONLY table’s data is wiped, so SQL Server blocks the relationship outright at design time.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Table Definitions

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance.
  • Run the following query to check durability settings on both tables:
SELECT name, durability_desc 
FROM sys.tables 
WHERE name IN ('YourParentTable', 'YourChildTable');
  • Confirm which table is set to SCHEMA_AND_DATA and which is set to SCHEMA_ONLY.

Step 2: Decide on a Durability Alignment Strategy

  • Determine whether the referenced (parent) table actually needs its data persisted across restarts.
  • If data persistence is required for both tables, plan to alter the SCHEMA_ONLY table to SCHEMA_AND_DATA.
  • If data loss on restart is acceptable for the relationship, plan to remove the durability mismatch by converting the parent table to SCHEMA_ONLY as well (only if business logic allows).

Step 3: Recreate the Table with Matching Durability

  • Since DURABILITY cannot be altered with a simple ALTER TABLE statement, you must drop and recreate the table:
DROP TABLE dbo.YourSchemaOnlyTable;

CREATE TABLE dbo.YourSchemaOnlyTable
(
    Id INT NOT NULL PRIMARY KEY NONCLUSTERED,
    -- other columns
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
  • Re-insert any required seed data since SCHEMA_ONLY tables lose all rows on restart.

Step 4: Re-Establish the Foreign Key Constraint

  • Once both tables share DURABILITY = SCHEMA_AND_DATA, add the foreign key:
ALTER TABLE dbo.YourChildTable
ADD CONSTRAINT FK_Child_Parent 
FOREIGN KEY (ParentId) REFERENCES dbo.YourSchemaOnlyTable(Id);
  • Validate the constraint was created successfully without triggering Error 10719.

Step 5: Rebuild Dependent Objects and Indexes

  • Check for natively compiled stored procedures or indexes that referenced the old table object (since drop/recreate changes the object_id).
  • Recompile any natively compiled modules using sp_recompile or by altering the procedure with WITH SCHEMABINDING re-validated.

Alternative or Verification Steps

  • Verify the fix: Re-run the sys.tables query from Step 1 to confirm both tables now report SCHEMA_AND_DATA, then attempt an INSERT on the child table referencing a valid parent key to confirm referential integrity is enforced.
  • Alternative workaround: If persisting data on the SCHEMA_ONLY table is not feasible (e.g., it’s used purely as a staging/temp table), remove the foreign key constraint entirely and enforce referential integrity at the application layer instead, since memory-optimized tables do not strictly require database-level FKs for performance-critical staging scenarios.