Creating indexes and constraints on encrypted columns

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.

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)