Regression: Azure SQL Does Not Support Cascade Delete On Temporal Tables

Question

Beginning with SQL Server 2017 CTP 2.0, temporal tables support cascade of delete and update.

Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations?view=azuresqldb-current

This limitation applies to SQL Server 2016 only. CASCADE options are supported in SQL Database and SQL Server 2017 starting from CTP 2.0.

If your Azure SQL DB's compatibility level is 150, then CASCADE delete on temporal tables should be supported.
SELECT compatibility_level
FROM sys.databases;

Temporal Table with Cascade Delete Test Case:

BEGIN TRANSACTION;  
  
CREATE TABLE [ParentTable] (  
    Id UNIQUEIDENTIFIER PRIMARY KEY,  
    Name NVARCHAR(100) NULL,  
    SystemEndTime DATETIME2 NOT NULL DEFAULT '9999-12-31 23:59:59.9999999',  
    SystemStartTime DATETIME2 NOT NULL);  
ALTER TABLE [ParentTable] ADD PERIOD FOR SYSTEM_TIME (SystemStartTime, SystemEndTime);  
ALTER TABLE [ParentTable] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[ParentTableHistory]));  
CREATE TABLE [ChildTable] (  
    Id UNIQUEIDENTIFIER PRIMARY KEY,  
    Name NVARCHAR(100) NULL,  
    ParentId UNIQUEIDENTIFIER NOT NULL,  
    SystemEndTime DATETIME2 NOT NULL DEFAULT '9999-12-31 23:59:59.9999999',  
    SystemStartTime DATETIME2 NOT NULL);  
ALTER TABLE [ChildTable] ADD PERIOD FOR SYSTEM_TIME (SystemStartTime, SystemEndTime);  
ALTER TABLE [ChildTable] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[ChildTableHistory]));  
-- This will give error Cannot define foreign key constraint 'FK_ChildTable_ParentTable_ParentId' with cascaded DELETE or UPDATE on table 'ChildTable' because table is system-versioned.  
ALTER TABLE [ChildTable]  
ADD CONSTRAINT FK_ChildTable_ParentTable_ParentId  
    FOREIGN KEY ([ParentId])  
    REFERENCES [ParentTable] ([Id])  
    ON DELETE CASCADE;  
  
ROLLBACK;  	  

When running this minimal test case, starting 12/2/2021, sometime mid-day, this no longer passes. On 12/2/2021 this case failed for all of our Azure SQL db's. Now, this morning, this test case is passing for about half of our Azure SQL DB's, but still failing in the most important ones, our Production DB's.

This is the error when it fails:

Failed to execute query. Error: Cannot define foreign key constraint 'FK_ChildTable_ParentTable_ParentId' with cascaded DELETE or UPDATE on table 'ChildTable' because table is system-versioned.
Could not create constraint or index. See previous errors.

Any idea when this Azure SQL issue will be patched and deployed to all rings?

Answer 1

Regression/outage has been resolved as of 6:52 PM CST. Awaiting requested RCA and Azure credit refund.

Answer 2

Update: I still have 1 DB that is able to reproduce this issue today.

Answer 3

Update: New DB's created using CREATE DATABASE ... AS COPY OF ...; exhibit the issue preventing us from creating copy's of DB that we need for our everyday operations. Still looks to be major outage.

Answer 4

Devin's comment is the correct answer:

This issue is due to a misconfiguration in Azure, if you encounter this please engage CSS to open a PG ticket so we can re-enable this feature for your logical server. The next Azure deployment will fix this automatically.