Primary Key and Default Constraint Lost after Apply Always Encrypt

Question

I already successfully setup always encrypt with secure enclave in SQL Server 2022 (with no attestation). Then I create table with this code.

CREATE TABLE dbo.tbTesting (
    idData INT PRIMARY KEY,
    descData VARCHAR(200),
    inputTime DATETIME DEFAULT GETDATE()
);
GO

The table is successfully created. Then I am using SSMS to encrypt descData column (right click the table then encrypt columns). Encryption type is Randomized. Encryption Key is enclave enabled. The column is success encrypted too.

But the problem is primary key in idData column is lost and default constraint in inputTime column is lost too.

I must recreate the primary key and default constraint.

ALTER TABLE dbo.tbTesting ADD PRIMARY KEY (idData);
ALTER TABLE dbo.tbTesting ADD CONSTRAINT dtInputTime DEFAULT GETDATE() FOR inputTime;

Is this is normal effect of always encrypt or a bug? Or something I don't know about always encrypt?

I use

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home Single Language 10.0 (Build 19045: ) (Hypervisor)

and my SSMS version is 19.1.56.0

Answer 1

This is an issue in the Wizard. I have asked engineering to look into this issue.

A workaround is indeed recreate the primary key and default constraint or use T-SQL

ALTER TABLE dbo.tbTesting
ALTER COLUMN [descData] VARCHAR(200) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO