Incorrect deployment plan order generated when a Temporal table with foreign key constraint is encrypted #440
Description
Create 2 system-versioned temporal tables & add FK dependency as below.
CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));
CREATE TABLE Department1
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory1));
ALTER TABLE [dbo].[Department] ADD CONSTRAINT [FK_Department_ManagerID] FOREIGN KEY([DeptID])
REFERENCES [dbo].[Department1] ([DeptID]);
Encrypt DeptName column of [dbo].[Department] table.
Actual: Exception is thrown that the foreign key references the table & hence the table can’t be dropped.
Expected: Encryption should be successful without any exceptions.