You can create an index on an encrypted column if the encryption key has been specified without any initialization vector or random padding. An error occurs if you execute create index on an encrypted column that has an initialization vector or random padding. Indexes on encrypted columns are generally useful for equality and nonequality matches. However, indexes are not useful for matching case-insensitive data, or for range searches of any data.
You cannot use an encrypted column in an expression
for a functional index.
In the following example, cc_key specifies encryption without using an initialization vector or padding. This allows an index to be built on any column encrypted with cc_key:
create encryption key cc_key with init_vector null create table customer(custid int, creditcard varchar(16) encrypt with cc_key) create index cust_idx on customer(creditcard)
You can encrypt a column that is declared as a primary or unique key.
You can define referential integrity constraints on encrypted columns when:
Both referencing and referenced columns are encrypted with the same key.
The key used to encrypt the columns specifies init_vector null and pad random has not been specified.
Referential integrity checks are efficient because they are performed on cipher text values.
In this example, ssn_key encrypts the ssn column in both the primary and foreign tables:
create encryption key ssn_key for AES with init_vector null create table user_info (ssn char(9) primary key encrypt with ssn_key, uname char(50), uaddr char(100)) create table tax_detail (ssn char(9) references user_info encrypt with ssn_key, return_info text)
