With_Clause for Create Table
Create table accepts the following options on the with clause, specified as a comma-separated list:
location = (location_name {, location_name})
Specifies the locations where the new table is created. To create locations, use the create location statement (see the Ingres SQL Reference Guide). The location_names must exist and the database must have been extended to the corresponding areas. If the location option is omitted, the table is created in the default database location. If multiple location_names are specified, the table is physically partitioned across the areas. For details about defining location names and extending databases, see the Ingres Database Administrator Guide.
[no]journaling
Explicitly enables or disables journaling on the table. For details about journaling, see the Database Administrator Guide.
To set the session default for journaling, use the set [no]journaling statement. The session default specifies the setting for tables created during the current session. To override the session default, specify the with [no]journaliing clause in the create table statement.
If journaling is enabled for the database and a table is created with journaling enabled, journaling begins immediately. If journaling is not enabled for the database and a table is created with journaling enabled, journaling begins when journaling is enabled for the entire database.
Note: To enable or disable journaling for the database and for system catalogs, use the ckpdb command. For information about ckpdb, see the Ingres Command Reference Guide.
[no]duplicates
Allows or disallows duplicate rows in the table. This option does not affect a table created as heap. Heap tables always accept duplicate rows regardless of the setting of this option.
If a heap table is created with noduplicates and is subsequently modified to a different table structure, the noduplicates option will be enforced with the result that rows which are totally identical will have the duplicates dropped without warning.
The duplicates setting can be overridden by specifying a unique key for a table in the modify statement.
Default: duplicates
page_size = n
Specifies a page size, in number of bytes. Valid values are described in Page_size Option.
Default: 2048. The tid size is 4.
The buffer cache for the installation must also be configured with the page size specified in create table or an error occurs.
security_audit = (audit_opt {, audit_opt})
Specifies row or table level auditing, as described in Security_audit Option.
security_audit_key = (column)
Writes an attribute to the audit log to uniquely identify the row in the security audit log. For example, an employee number can be used as the security audit key.
[no]autostruct
Specifies whether the storage structure of the table should automatically default to B-tree (with autostruct) or to heap (with noautostruct), in combination with constraint definitions in create table.
encryption=encryption type, [aeskey=hex-aes-key,] passphrase='encryption-passphrase'
Specifies encryption options to secure data in a column defined with the ENCRYPT option:
encryption type
Specifies the type of Advanced Encryption Standard (AES) encryption for the column:
AES128
128-bit encryption
AES192
192-bit encryption
AES256
256-bit encryption
aeskey=
Specifies the internal key used to encrypt the user data. The key is protected by a key derived from the passphrase. The aeskey must be the exact hexadecimal value of the key, which depends on whether AES128, AES192, or AES156 is specified (thus 16, 24, or 32 bytes) and is in the usual Ingres hexadecimal constant format of x'03010401050902060503050901040104' or 0x03010401050902060503050901040104.
If no aeskey is specified, a random key is generated.
passphrase='encryption passphrase'
Specifies the encryption passphrase used to encrypt and decrypt the column data. The passphrase must be at least eight characters, can contain spaces, and must be specified within single quotation marks (' '). The string is converted to the appropriate AES key size (16, 24, or 32 bytes).
Page_size Option
The page_size option on the with clause in the create table statement creates a table with a specific page size. This option has the following format:
page_size = n
where n is the number of bytes.
Valid values are shown in the Number of Bytes column in the following table:
Security_audit Option
The security_audit option on the with clause specifies the auditing level.
This option has the following format:
security_audit = (table_audit_opt {, table_audit_opt})
table_audit_opt
Specifies the level of security, as follows:
table
(Default) Implements table-level security auditing on general operations (for example create, drop, modify, insert, or delete) performed on the table.
[no]row
Implements row-level security auditing on operations performed on individual rows, such as insert, delete, update, or select. If norow is specified, the row-level security auditing is not implemented.
For example, an SQL delete statement that deleted 500 rows from a table with both table and row auditing generates the following audit events:
• One table-delete audit event, indicating the user issued a delete against the table.
• 500 row-delete audit events, indicating which rows were deleted.
Note: Either table and row or table and norow auditing can be specified. If norow is specified, row-level auditing is not performed. If either clause is omitted, the default installation row auditing is used. The default can be either row or norow depending on how your installation is configured.
With Security_audit_key Clause
The with security_audit_key clause allows the user to specify an optional attribute to be written to the audit log to assist row or table auditing. For example, an employee number can be used as the security audit key:
create table employee (name char(60), emp_no integer)
with security_audit = (table, row),
security_audit_key = (emp_no);
If no user-specified attribute is given and the table has row-level auditing, a new hidden attribute, _ii_sec_tabkey of type TABLE_KEY SYSTEM_MAINTAINED is created for the table to be used as the row audit key. Although any user attribute can be used for the security audit key (security_audit_key clause), we recommend that a short, distinctive value be used (such as a social security ID), allowing the user to uniquely identify the row when reviewing the security audit log. If an attribute longer than 256 bytes is specified for the security audit key, only the first 256 bytes are written to the security audit log.