CREATE TABLE Statement

(back to top)

• { ENABLE | DISABLE } RLV STORE registers this table with the RLV store for real-time in-memory updates. Not supported for IQ temporary tables. This value overrides the value of the database option BASE_TABLES_IN_RLV. In a multiplex, the RLV store can only be enabled on the coordinator.

• IN used in the column-definition, column-constraint, table-constraint, foreign-key, and partition-decl clauses to specify the dbspace where the object is to be created. If the IN clause is omitted, SAP IQ creates the object in the dbspace where the table is assigned.

  Specify SYSTEM with this clause to put either a permanent or temporary table in the catalog store. Specify IQ_SYSTEM_TEMP to store temporary user objects (tables, partitions, or table indexes) in IQ_SYSTEM_TEMP or, if the TEMP_DATA_IN_SHARED_TEMP option is set 'ON', and the IQ_SHARED_TEMP dbspace contains RW files, in IQ_SHARED_TEMP. (You cannot specify the IN clause with IQ_SHARED_TEMP.) All other use of the IN clause is ignored. By default, all permanent tables are placed in the main IQ store, and all temporary tables are placed in the temporary IQ store. Global temporary and local temporary tables can never be in the IQ store.

  The following syntax is unsupported:

  CREATE LOCAL TEMPORARY TABLE tab1(c1 int) IN IQ_SHARED_TEMP

  A BIT data type column cannot be explicitly placed in a dbspace. The following is not supported for BIT data types:

  CREATE TABLE t1(c1_bit bit IN iq_main); 

• ON COMMIT allowed for temporary tables only. By default, the rows of a temporary table are deleted on COMMIT.

• AT creates a proxy table that maps to a remote location specified by the location-string clause. Proxy table names must be 30 characters or less. The AT clause supports semicolon (;) delimiters. If a semicolon is present anywhere in the location-string clause, the semicolon is the field delimiter. If no semicolon is present, a period is the field delimiter. This allows file names and extensions to be used in the database and owner fields.

  Semicolon field delimiters are used primarily with server classes not currently supported; however, you can also use them in situations where a period would also work as a field delimiter. For example, this statement maps the table proxy_a to the SAP SQL Anywhere database mydb on the remote server myasa:

  CREATE TABLE proxy_a1
  AT 'myasa;mydb;;a1'

  Foreign-key definitions are ignored on remote tables. Foreign-key definitions on local tables that refer to remote tables are also ignored. Primary key definitions are sent to the remote server if the server supports primary keys.

  In a simplex environment, you cannot create a proxy table that refers to a remote table on the same node. In a multiplex environment, you cannot create a proxy table that refers to the remote table defined within the multiplex.

• IF NOT EXISTS if the named object already exists, no changes are made and an error is not returned.

• column-definition defines a table column. Allowable data types are described in Reference: Building Blocks, Tables, and Procedures >SQL Data Types. Two columns in the same table cannot have the same name. You can create up to 45,000 columns; however, there might be performance penalties in tables with more than 10,000 columns.
  • [ NOT ] NULL] includes or excludes NULL values. If NOT NULL is specified, or if the column is in a UNIQUE or PRIMARY KEY constraint, the column cannot contain any NULL values. The limit on the number of columns per table that allow NULLs is approximately 8*(database-page-size - 30).
  • DEFAULT default-value specify a default column value with the DEFAULT keyword in the CREATE TABLE (and ALTER TABLE) statement. A DEFAULT value is used as the value of the column in any INSERT (or LOAD) statement that does not specify a column value.
  • DEFAULT AUTOINCREMENT the value of the DEFAULT AUTOINCREMENT column uniquely identifies every row in a table. Columns of this type are also known as IDENTITY columns, for compatibility with SAP ASE. The IDENTITY/DEFAULT AUTOINCREMENT column stores sequential numbers that are automatically generated during inserts and updates. When using IDENTITY or DEFAULT AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type, with scale 0. The column value might also be NULL. You must qualify the specified table name with the owner name.

    ON inserts into the table. If a value is not specified for the IDENTITY/DEFAULT AUTOINCREMENT column, a unique value larger than any other value in the column is generated. If an INSERT specifies a value for the column, it is used; if the specified value is not larger than the current maximum value for the column, that value is used as a starting point for subsequent inserts.

    Deleting rows does not decrement the IDENTITY/AUTOINCREMENT counter. Gaps created by deleting rows can only be filled by explicit assignment when using an insert.

    Note The database option IDENTITY_INSERT must be set to the table name to perform an explicit insert or update into an IDENTITY/AUTOINCREMENT column. For information on IDENTITY_INSERT, see Reference: Statements and Options.

    For example, this creates a table with an IDENTITY column and explicitly adds some data to it:

    CREATE TABLE mytable(c1 INT IDENTITY);
    SET TEMPORARY OPTION IDENTITY_INSERT = "DBA".mytable;
    INSERT INTO mytable VALUES(5);

    After an explicit insert of a row number less than the maximum, subsequent rows without explicit assignment are still automatically incremented with a value of one greater than the previous maximum.

    You can find the most recently inserted value of the column by inspecting the @@identity global variable.

  • IDENTITY a Transact-SQL® -compatible alternative to using the AUTOINCREMENT default. In SAP IQ, the identity column may be created using either the IDENTITY or the DEFAULT AUTOINCREMENT clause.

• table-constraint helps ensure the integrity of data in the database. There are four types of integrity constraints:
  • UNIQUE identifies one or more columns that uniquely identify each row in the table. No two rows in the table can have the same values in all the named columns. A table may have more than one unique constraint.
  • PRIMARY KEY the same as a UNIQUE constraint except that a table can have only one primary-key constraint. You cannot specify the PRIMARY KEY and UNIQUE constraints for the same column. The primary key usually identifies the best identifier for a row. For example, the customer number might be the primary key for the customer table.
  • FOREIGN KEY restricts the values for a set of columns to match the values in a primary key or uniqueness constraint of another table. For example, a foreign-key constraint could be used to ensure that a customer number in an invoice table corresponds to a customer number in the customer table.

    You cannot create foreign-key constraints on local temporary tables. Global temporary tables must be created with ON COMMIT PRESERVE ROWS.

  • CHECK allows arbitrary conditions to be verified. For example, a check constraint could be used to ensure that a column called Gender contains only the values male or female. No row in a table is allowed to violate a constraint. If an INSERT or UPDATE statement would cause a row to violate a constraint, the operation is not permitted and the effects of the statement are undone.

    Column identifiers in column check constraints that start with the symbol ‘@’ are placeholders for the actual column name. A statement of the form:

    CREATE TABLE t1(c1 INTEGER CHECK (@foo < 5))

    is exactly the same as this statement:

    CREATE TABLE t1(c1 INTEGER CHECK (c1 < 5))

    Column identifiers appearing in table check constraints that start with the symbol ‘@’are not placeholders.

  If a statement would cause changes to the database that violate an integrity constraint, the statement is effectively not executed and an error is reported. (Effectively means that any changes made by the statement before the error was detected are undone.)

  SAP IQ enforces single-column UNIQUE constraints by creating an HG index for that column.

  Note You cannot define a column with a BIT data type as a UNIQUE or PRIMARY KEY constraint. Also, the default for columns of BIT data type is to not allow NULL values; you can change this by explicitly defining the column as allowing NULL values.

• column-constraint restricts the values the column can hold. Column and table constraints help ensure the integrity of data in the database. If a statement would cause a violation of a constraint, execution of the statement does not complete, any changes made by the statement before error detection are undone, and an error is reported. Column constraints are abbreviations for the corresponding table constraints. For example, these are equivalent:

  CREATE TABLE Products (
  	product_num integer UNIQUE
  )
  CREATE TABLE Products (
  	product_num integer,
  	UNIQUE ( product_num )
  )

  Column constraints are normally used unless the constraint references more than one column in the table. In these cases, a table constraint must be used.

  • IQ UNIQUE defines the expected cardinality of a column and determines whether the column loads as Flat FP or NBit FP. An IQ UNIQUE(n) value explicitly set to 0 loads the column as Flat FP. Columns without an IQ UNIQUE constraint implicitly load as NBit up to the limits defined by the FP_NBIT_AUTOSIZE_LIMIT, FP_NBIT_LOOKUP_MB, and FP_NBIT_ROLLOVER_MAX_MB options:
    • FP_NBIT_AUTOSIZE_LIMIT limits the number of distinct values that load as NBit
    • FP_NBIT_LOOKUP_MB sets a threshold for the total NBit dictionary size
    • FP_NBIT_ROLLOVER_MAX_MB sets the dictionary size for implicit NBit rollovers from NBit to Flat FP
    • FP_NBIT_ENFORCE_LIMITS enforces NBit dictionary sizing limits. This option is OFF by default
    Using IQ UNIQUE with an n value less than the FP_NBIT_AUTOSIZE_LIMIT is not necessary. Auto-size functionality automatically sizes all low or medium cardinality columns as NBit. Use IQ UNIQUE in cases where you want to load the column as Flat FP or when you want to load a column as NBit when the number of distinct values exceeds the FP_NBIT_AUTOSIZE_LIMIT.
  Note

  • Consider memory usage when specifying high IQ UNIQUE values. If machine resources are limited, avoid loads with FP_NBIT_ENFORCE_LIMITS='OFF' (default).

    Prior to SAP IQ 16.0, an IQ UNIQUE <n> value > 16777216 would rollover to Flat FP. In 16.0, larger IQ UNIQUE values are supported for tokenization, but may require significant memory resource requirements depending on cardinality and column width.

  • BIT, BLOB,and CLOB data types do not support NBit dictionary compression. If FP_NBIT_IQ15_COMPATIBILITY=’OFF’, a non-zero IQ UNIQUE column specification in a CREATE TABLE or ALTER TABLE statement that includes these data types returns an error.

• column-constraint and table-constraint clauses column and table constraints help ensure the integrity of data in the database.
  • PRIMARY KEY or PRIMARY KEY ( column-name, … ) the primary key for the table consists of the listed columns, and none of the named columns can contain any NULL values. SAP IQ ensures that each row in the table has a unique primary key value. A table can have only one PRIMARY KEY.

    When the second form is used (PRIMARY KEY followed by a list of columns), the primary key is created including the columns in the order in which they are defined, not the order in which they are listed.

    When a column is designated as PRIMARY KEY, FOREIGN KEY, or UNIQUE, SAP IQ creates a High_Group index for it automatically. For multicolumn primary keys, this index is on the primary key, not the individual columns. For best performance, you should also index each column with a HG or LF index separately.

  • REFERENCES primary-table-name [(primary-column-name)] defines the column as a foreign key for a primary key or a unique constraint of a primary table. Normally, a foreign key would be for a primary key rather than an unique constraint. If a primary column name is specified, it must match a column in the primary table which is subject to a unique constraint or primary key constraint, and that constraint must consist of only that one column. Otherwise the foreign key references the primary key of the second table. Primary key and foreign key must have the same data type and the same precision, scale, and sign. Only a non unique single-column HG index is created for a single-column foreign key. For a multicolumn foreign key, SAP IQ creates a non unique composite HG index. The maximum width of a multicolumn composite key for a unique or non unique HG index is 1KB.

    A temporary table cannot have a foreign key that references a base table and a base table cannot have a foreign key that references a temporary table. Local temporary tables cannot have or be referenced by a foreign key.

  • FOREIGN KEY [role-name] [(...)] REFERENCES primary-table-name [(...)] defines foreign-key references to a primary key or a unique constraint in another table. Normally, a foreign key would be for a primary key rather than an unique constraint. (In this description, this other table is called the primary table.)

    If the primary table column names are not specified, the primary table columns are the columns in the table's primary key. If foreign key column names are not specified, the foreign-key columns have the same names as the columns in the primary table. If foreign-key column names are specified, then the primary key column names must be specified, and the column names are paired according to position in the lists.

    If the primary table is not the same as the foreign-key table, either the unique or primary key constraint must have been defined on the referenced key. Both referenced key and foreign key must have the same number of columns, of identical data type with the same sign, precision, and scale.

    The value of the row's foreign key must appear as a candidate key value in one of the primary table's rows unless one or more of the columns in the foreign key contains nulls in a null allows foreign key column.

    Any foreign-key column not explicitly defined is automatically created with the same data type as the corresponding column in the primary table. These automatically created columns cannot be part of the primary key of the foreign table. Thus, a column used in both a primary key and foreign key must be explicitly created.

    <role-name> is the name of the foreign key. The main function of <role-name> is to distinguish two foreign keys to the same table. If no <role-name> is specified, the role name is assigned as follows:

    1. If there is no foreign key with a <role-name> the same as the table name, the table name is assigned as the <role-name>.
    2. If the table name is already taken, the <role-name> is the table name concatenated with a zero-padded 3-digit number unique to the table.

    The referential integrity action defines the action to be taken to maintain foreign-key relationships in the database. Whenever a primary key value is changed or deleted from a database table, there may be corresponding foreign key values in other tables that should be modified in some way. You can specify an ON DELETE clause, followed by the RESTRICT clause.

  • RESTRICT generates an error if you try to update or delete a primary key value while there are corresponding foreign keys elsewhere in the database. Generates an error if you try to update a foreign key so that you create new values unmatched by a candidate key. This is the default action, unless you specify that LOAD optionally reject rows that violate referential integrity. This enforces referential integrity at the statement level.

    If you use CHECK ON COMMIT without specifying any actions, then RESTRICT is implied as an action for DELETE. SAP IQ does not support CHECK ON COMMIT.

    a global temporary table cannot have a foreign key that references a base table and a base table cannot have a foreign key that references a global temporary table. Local temporary tables cannot have or be referenced by a foreign key.

  • CHECK ( condition ) no row is allowed to fail the condition. If an INSERT statement would cause a row to fail the condition, the operation is not permitted and the effects of the statement are undone.

    The change is rejected only if the condition is FALSE; in particular, the change is allowed if the condition is UNKNOWN. CHECK condition is not enforced by SAP IQ.

    Note

    If possible, do not define referential integrity foreign key-primary key relationships in SAP IQ unless you are certain there are no orphan foreign keys.

• Remote Tables foreign-key definitions are ignored on remote tables. Foreign-key definitions on local tables that refer to remote tables are also ignored. Primary-key definitions are sent to the remote server if the server supports it.

• PARTITION BY divides large tables into smaller, more manageable storage objects. Partitions share the same logical attributes of the parent table, but can be placed in separate dbspaces and managed individually. SAP IQ supports several table partitioning schemes:
  • hash-partitions
  • range-partitions
  • composite-partitions
  A partition-key is the column or columns that contain the table partitioning keys. Partition keys can contain NULL and DEFAULT values, but cannot contain:

  • LOB (BLOB or CLOB) columns
  • BINARY, or VARBINARY columns
  • CHAR or VARCHAR columns whose length is over 255 bytes
  • BIT columns
  • FLOAT/DOUBLE/REAL columns

• PARTITION BY RANGE partitions rows by a range of values in the partitioning column. Range partitioning is restricted to a single partition key column and a maximum of 1024 partitions. In a range-partitioning-scheme, the partition-key is the column that contains the table partitioning keys:

  range-partition-decl:
    <partition-name> VALUES <= ( {<constant-expr> |  MAX } [ , { <constant-expr> |  MAX }]... ) 
      [ IN <dbspace-name> ]

  The partition-name is the name of a new partition on which table rows are stored. Partition names must be unique within the set of partitions on a table. The partition-name is required.
  • VALUE specifies the inclusive upper bound for each partition (in ascending order). The user must specify the partitioning criteria for each range partition to guarantee that each row is distributed to only one partition. NULLs are allowed for the partition column and rows with NULL as partition key value belong to the first table partition. However, NULL cannot be the bound value.

    There is no lower bound (MIN value) for the first partition. Rows of NULL cells in the first column of the partition key will go to the first partition. For the last partition, you can either specify an inclusive upper bound or MAX. If the upper bound value for the last partition is not MAX, loading or inserting any row with partition key value larger than the upper bound value of the last partition generates an error.

  • Max denotes the infinite upper bound and can only be specified for the last partition.
  • IN specifies the dbspace in the partition-decl on which rows of the partition should reside.
  These restrictions affect partitions keys and bound values for range partitioned tables:

  • Partition bounds must be constants, not constant expressions.
  • Partition bounds must be in ascending order according to the order in which the partitions were created. That is, the upper bound for the second partition must be higher than for the first partition, and so on.

    In addition, partition bound values must be compatible with the corresponding partition-key column data type. For example, VARCHAR is compatible with CHAR.

  • If a bound value has a different data type than that of its corresponding partition key column, SAP IQ converts the bound value to the data type of the partition key column, with these exceptions:
  • Explicit conversions are not allowed. This example attempts an explicit conversion from INT to VARCHAR and generates an error:

    CREATE TABLE Employees(emp_name VARCHAR(20)) 
    PARTITION BY RANGE(emp_name)
    (p1 VALUES <=(CAST (1 AS VARCHAR(20))), 
    p2 VALUES <= (CAST (10 AS VARCHAR(20)))

  • Implicit conversions that result in data loss are not allowed. In this example, the partition bounds are not compatible with the partition key type. Rounding assumptions may lead to data loss and an error is generated:

    CREATE TABLE emp_id (id INT) PARTITION BY RANGE(id) (p1 VALUES <= (10.5), p2 VALUES <= (100.5))

  • In this example, the partition bounds and the partition key data type are compatible. The bound values are directly converted to float values. No rounding is required, and conversion is supported:

    CREATE TABLE id_emp (id FLOAT)
    PARTITION BY RANGE(id) (p1 VALUES <= (10), 
    p2 VALUES <= (100))

  • Conversions from non-binary data types to binary data types are not allowed. For example, this conversion is not allowed and returns an error:

    CREATE TABLE newemp (name BINARY)
    PARTITION BY RANGE(name) 
    (p1 VALUES <= ("Maarten"), 
    p2 VALUES <= ("Zymmerman")

  • NULL cannot be used as a boundary in a range-partitioned table.
  • The row will be in the first partition if the cell value of the 1st column of the partition key evaluated to be NULL. SAP IQ supports only single column partition keys, so any NULL in the partition key distributes the row to the first partition.

• PARTITION BY HASH maps data to partitions based on partition-key values processed by an internal hashing function. Hash partition keys are restricted to a maximum of eight columns with a combined declared column width of 5300 bytes or less. For hash partitions, the table creator determines only the partition key columns; the number and location of the partitions are determined internally.
  In a hash-partitioning declaration, the partition-key is a column or group of columns, whose composite value determines the partition where each row of data is stored:

  hash-partitioning-scheme: 
    HASH  ( partition-key [ , partition-key, … ] )

  • Restrictions
    • You can only hash partition a base table. Attempting to partitioning a global temporary table or a local temporary table raises an error.
    • You cannot add, drop, merge, or split a hash partition.
    • You cannot add or drop a column from a hash partition key.
• PARTITION BY HASH RANGE subpartitions a hash-partitioned table by range. In a hash-range-partitioning-scheme declaration, a SUBPARTITION BY RANGE clause adds a new range subpartition to an existing hash-range partitioned table:

  hash-range-partitioning-scheme:                
  PARTITION BY HASH  ( partition-key [ , partition-key, … ] )
      [ SUBPARTITION BY RANGE  ( range-partition-decl [ , range-partition-decl ... ] ) ]

  The hash partition specifies how the data is logically distributed and colocated; the range subpartition specifies how the data is physically placed. The new range subpartition is logically partitioned by hash with the same hash partition keys as the existing hash-range partitioned table. The range subpartition key is restricted to one column.
  • Restrictions
    • You can only hash partition a base table. Attempting to partitioning a global temporary table or a local temporary table raises an error.
    • You cannot add, drop, merge, or split a hash partition.
    • You cannot add or drop a column from a hash partition key.
    Note

    Range-partitions and composite partitioning schemes, like hash-range partitions, require the separately licensed VLDB Management option.