Column definition

Use the column definition segment of the CREATE TABLE statement to declare the name and data type (and optionally the default value and the constraints or the security label) of a single column of the new table.

This syntax fragment is part of the CREATE TABLE statement.
(1)
Column Definition

1  column  %Data Type1
1 ? %DEFAULT Clause2? %Single-Column Constraint Format3
1 ? %Single-Column Constraint Format3? %DEFAULT Clause2
2?  %Column SECURED WITH label clause4
Notes:
Element Description Restrictions Syntax
column Name that you declare here for a column in the table Must be unique in this table Identifier

Usage

Because the maximum row size is 32,767 bytes, no more than approximately 97 columns can be of COLLECTION data types (SET, LIST, and MULTISET). No more than approximately 195 columns in the table can be of the data types BYTE, TEXT, ROW, LVARCHAR, NVARCHAR, VARCHAR, and varying-length UDTs. (Here 195 columns is an approximate lower limit that applies to platforms with a 2 KB base page size. For platforms with a base page size of 4 KB, such as Windows™ and AIX® systems, the upper limit is approximately 450 columns of these data types.)

The upper limit on the number of columns of these data types also depends on other data that describes the table that the database server stores in the same partition. For some tables, the maximum number of columns might be lower, if the aggregate length of all the SQL identifiers (including the database name, table names, and index names) that are compressed and stored on the disk reduces the free space that is available for the columns.

Character column size semantics

Any explicit or default storage size specifications for columns of built-in character types, such as CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR, are interpreted in units of bytes, unless the SQL_LOGICAL_CHAR configuration parameter is set to enable logical character semantics in data type declarations.

Interpreting size declarations as logical character semantics reduces the risk of insufficient storage for column values in INSERT and UPDATE operations When the data length exceeds the maximum size of the column, then the result depends on the ANSI-compliance status of the database:
  • If the database is not ANSI-compliant, HCL OneDB™ truncates the value. No warning is generated when this truncation occurs.
  • If the database is ANSI-compliant, then the INSERT or UPDATE operation fails and this error is returned:
      -1279: Value exceeds string column length.

See the HCL OneDB Administrator's Reference description of the SQL_LOGICAL_CHAR configuration parameter for more information about the effect of its setting in locales that support a multibyte code set, such as UTF-8, where a single logical character can require more than one byte of storage.

Restrictions on IDSSECURITYLABEL columns

The following restrictions affect the use of the Column Definition clause to specify a column of the IDSSECURITYLABEL data type to support label-based access control (LBAC):
  • If the table has no security policy, a user who holds the DBSECADM role must also include the SECURITY POLICY clause to specify a security policy.
  • Only a user who holds the DBSECADM role can specify a column of type IDSSECURITYLABEL.
  • A table can have at most one column of type IDSSECURITYLABEL.
  • The IDSSECURITYLABEL column cannot have column protection.
  • The IDSSECURITYLABEL column has an implicit NOT NULL constraint by default. If no label name for the default security label is specified in the DEFAULT clause, the default value for this column is the security label for write access that is held by the user.
  • The IDSSECURITY LABEL column cannot have any explicit single-column constraints, and it cannot be part of multiple-column referential or check constraints.
  • The IDSSECURITYLABEL column cannot be encrypted.
  • If the table is secured with both row-level and column-level protection, the default or explicit security label of the IDSSECURITYLABEL column must have the same security policy as any labels that the Column SECURED WITH clause references.

As with any SQL identifier, syntactic ambiguities (and sometimes error messages or unexpected behavior) can occur if the column name is a keyword, or if it is the same as the table name, or the name of another table that you later join with the table). For information about the keywords of HCL OneDB, see Keywords of SQL for HCL OneDB.

If you define a column of a table as a named ROW type, the table does not adopt any constraints of the named ROW.