2

Seeking clarification on this documentation.

Encryption is not supported for:

Columns with the IDENTITY property

I want to make sure that one of the columns can be an IDENTITY column if it is not encrypted but is involved in a FOREIGN-KEY constraint where the foreign counterpart that points back to it is indeed encrypted.

Let's say that in SQL Server 2016 or later we have a CLIENT table:

id int IDENTITY (1,1) NOT NULL  [column is primary key]
clientname varchar(100)

[NOTE: CLIENT id has high cardinality. Almost 200,000 clients in this table.]

and a MEDICALPROCEDURES table:

id int PK
procedurecode varchar(10)
clientid  int

and now a foreign key constraint:

ALTER TABLE MEDICALPROCEDURES
ADD CONSTRAINT FK_MEDICALPROCEDURES_CLIENT
FOREIGN KEY(clientid) REFERENCES CLIENT(id)

Now, if column MEDICALPROCEDURES.clientid is deterministically encrypted, and column CLIENT.id is left unencrypted, will the foreign key constraint succeed?

And will queries like the following succeed transparently?

select 
C.clientname, MP.procedurecode
from CLIENT c inner join MEDICALPROCEDURES MP
on C.id=MP.clientid
where C.clientid=12345
Tim
  • 8,669
  • 31
  • 105
  • 183

1 Answers1

2

It will not work.
From documentation following is not supported: "Referencing columns in foreign key constraints when using randomized encryption or when using deterministic encryption, if the referenced and referencing columns use different keys or algorithms". So you need to use the same deterministic encryption for id and clientid.

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17
  • That remark in the documentation wasn't clear to me. The unencrypted column, CLENT.id is not using any encryption algorithm at all, so I wondered, did that qualify as a "different algorithm"? It's not quite the same as saying *All columns in a foreign key constraint must use deterministic encryption and the same keys*. – Tim May 23 '19 at 16:18
  • In always encrypted SQL Server engine should never know the unencrypted value. Only the client driver has the keys. So join C.id=MP.clientid can not be performed on the server. (secure enclaves in SQL Server 2019 changes this) – Piotr Palka May 23 '19 at 16:49
  • That makes sense. Thanks for the information on "secure enclaves". – Tim May 23 '19 at 18:29
  • In SQL Server 2019 can a foreign key relationship exist on Client.id and MedicalProcedures.clientid if both columns are not integer but `uniqueidentifier` and the encryption is not deterministic but randomized? I haven't yet found the documentation on the limitations. – Tim May 23 '19 at 18:44