Creating Tables with Encrypted Columns

Considerations for creating tables with encrypted columns.

You can encrypt these datatypes:

  • int, smallint, tinyint

  • unsigned int, unsigned smallint, unsigned tinyint

  • bigint, unsigned bigint

  • decimal, numeric

  • float4, float8

  • money, smallmoney

  • date, time, smalldatetime, datetime, bigdatetime

  • char, varchar

  • unichar, univarchar

  • binary, varbinary

  • bit

The underlying datatype of encrypted data on disk is varbinary. Null values are not encrypted.

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

During create table, alter table, and select into operations, the SAP ASE server calculates the maximum internal length of the encrypted column. The database owner must know the maximum length of the encrypted columns before he or she can make decisions about schema arrangements and page sizes.

You can create an index on an encrypted column if you specify the encryption key without any initialization vector or random padding. Adpative Server issues an error if you execute create index on an encrypted column with an initialization vector or random padding.

You can define referential integrity constraints on encrypted columns when:

  • Both referencing and referenced columns are encrypted.

  • The key you use to encrypt the columns specifies init_vector null and you have not specified pad random.

You cannot encrypt a computed column, and an encrypted column cannot appear in the expression defining a computed column. You cannot specify an encrypted column in the partition_clause of create table.

See Encrypted Data in the Encrypted Columns Users Guide.

Parent topic: create table