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