create table

Use the encrypt qualifier with create table to set up encryption on a table column and optionally specify a decrypt default.

The following partial create table syntax only includes clauses specific to encryption. See the Reference Manual for the complete syntax:

Syntax

create table tablename (colname datatype [default_clause]
	[identity_clause][column_constraints]
	[encrypt [with [database.[owner].]keyname]
		[decrypt default constant expression]]
	[, next_colname datatype [optional clauses]]

keyname – identifies a key created using create encryption key. The creator of the table must have select permission on keyname. If keyname is not supplied, Adaptive Server looks for a default key created using the as default clause on create encryption key or alter encryption key.

You cannot reference a column in the partition_clause of create table that is specified for encryption in the target table.

Example

Example 1 Creates an employee table with a nullable encrypted column. Adaptive Server uses the database default encryption key to encrypt the ssn data:

create table employee_table (ssn char(15) null 
   encrypt name char(50), deptid int)

Example 2 To create a customer table with an encrypted column for credit card data, enter:

create table customer (ccard char(16) unique 
   encrypt with cc_key
   decrypt_default 'XXXXXXXXXXXXXXXX', name char(30))

The ccard column has a unique constraint and uses cc_key for encryption. Because of the decrypt_default specifier, Adaptive Server returns the value ‘XXXXXXXXXXXXXXXX’ instead of the actual data when a user without decrypt permission selects the ccard column.

Usage

create table displays an error if you:

Specify a computed column based on an expression that references one or more encrypted columns.
Use the encrypt and compute parameters on the same column.
List an encrypted column in the partition clause