CREATE TABLE Statement

Creates a new table in the database or on a remote server.

Syntax

CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE
 [ IF NOT EXISTS ] [ owner. ]table-name
… ( column-definition [ column-constraint ] … 
[ , column-definition [ column-constraint ] …]
[ , table-constraint ] … ) 
|{ ENABLE | DISABLE } RLV STORE 
|[WITH NULLS NOT DISTINCT]
…[ IN dbspace-name ]
…[ ON COMMIT { DELETE | PRESERVE } ROWS 
| NOT TRANSACTIONAL ]
[ AT location-string ]
[PARTITION BY 
  range-partitioning-scheme
  | hash-partitioning-scheme 
  | composite-partitioning-scheme ]

Parameters

column-definition –

column-name data-type 
    [ [ NOT ] NULL ] 
    [ DEFAULT default-value | IDENTITY ] 
    [ PARTITION | SUBPARTITION ( partition-name IN  dbspace-name [ , ... ] ) ]

default-value –

special-value
   | string
   | global variable
   | [ - ] number
   | ( constant-expression )
   | built-in-function( constant-expression )
   | AUTOINCREMENT
   | CURRENT DATABASE
   | CURRENT REMOTE USER
   | NULL
   | TIMESTAMP
   | LAST USER

special-value –

CURRENT 
  { DATE
    | TIME
    | TIMESTAMP
    | USER
    | PUBLISHER }
    | USER

column-constraint –

[ CONSTRAINT constraint-name ] {
     { UNIQUE  
        | PRIMARY KEY  
        | REFERENCES table-name [ ( column-name ) ] [ action ]  
      }
      [ IN dbspace-name ]
      | CHECK ( condition )
      | IQ UNIQUE ( integer ) 
    }

table-constraint –

[ CONSTRAINT constraint-name ] 
  { 
    { UNIQUE ( column-name [ , column-name ] … ) 
      | PRIMARY KEY ( column-name [ , column-name ] … ) 
     } 
    [ IN dbspace-name ] 
      | foreign-key-constraint
      | CHECK ( condition ) 
      | IQ UNIQUE ( integer ) 
   }

foreign-key-constraint –

FOREIGN KEY [ role-name ] [ ( column-name [ , column-name ] … ) ] 
   …REFERENCES table-name [ ( column-name [ , column-name ] … ) ]
   …[ actions ] [ IN dbspace-name ]

actions –
```
[ ON { UPDATE | DELETE } RESTRICT ]
```

location-string –

{ remote-server-name. [ db-name ].[ owner ].object-name
      | remote-server-name; [ db-name ];[ owner ];object-name }

Partitioning-scheme –

{ range-partitioning-scheme
      | hash-partitioning-scheme
      | composite-partitioning-scheme
    }

range-partitioning-scheme –

RANGE( partition-key ) ( range-partition-decl [,range-partition-decl ... ] )

partition-key –
```
column-name
```

range-partition-decl –

   VALUES <= ( {constant-expr |  MAX } [ , { constant-expr |  MAX }]... ) 
    [ IN dbspace-name ]

hash-partitioning-scheme –

HASH  ( partition-key [ , partition-key, … ] )

composite-partitioning-scheme –

hash-partitioning-scheme  SUBPARTITION  range-partitioning-scheme

Examples

Example 1 – Create a table named SalesOrders2 with five columns. Data pages for columns FinancialCode, OrderDate, and ID are in dbspace Dsp3. Data pages for integer column CustomerID are in dbspace Dsp1. Data pages for CLOB column History are in dbspace Dsp2. Data pages for the primary key, HG for ID, are in dbspace Dsp4:
```
CREATE TABLE SalesOrders2 (
FinancialCode CHAR(2),
CustomerID int IN Dsp1,
History CLOB IN Dsp2,
OrderDate TIMESTAMP,
ID BIGINT,
PRIMARY KEY(ID) IN Dsp4 
) IN Dsp3
```
Example 2 – Create a table fin_code2 with four columns. Data pages for columns code, type, and id are in the default dbspace, which is determined by the value of the database option DEFAULT_DBSPACE. Data pages for CLOB column description are in dbspace Dsp2. Data pages from foreign key fk1, HG for c1 are in dbspace Dsp4:
```
CREATE TABLE fin_code2 (
code INT,
type CHAR(10),
description CLOB IN Dsp2,
id BIGINT,
FOREIGN KEY fk1(id) REFERENCES SalesOrders(ID) IN Dsp4
)
```

Example 3 – Create a table t1 where partition p1 is adjacent to p2 and partition p2 is adjacent to p3:

CREATE TABLE t1 (c1 INT, c2 INT)
PARTITION BY RANGE(c1) 
(p1 VALUES <= (0), p2 VALUES <= (10), p3 VALUES <= (100))

Example 4 – Create a RANGE partitioned table bar with six columns and three partitions, mapping data to partitions based on dates:

CREATE TABLE bar (
		c1 INT IQ UNIQUE(65500),
		c2 VARCHAR(20),
		c3 CLOB PARTITION (P1 IN Dsp11, P2 IN Dsp12,
		   P3 IN Dsp13),
		c4 DATE,
		c5 BIGINT,
		c6 VARCHAR(500) PARTITION (P1 IN Dsp21,
		   P2 IN Dsp22),
		PRIMARY KEY (c5) IN Dsp2) IN Dsp1
		PARTITION BY RANGE (c4)
		(P1 VALUES <= ('2006/03/31') IN Dsp31, 
		 P2 VALUES <= ('2006/06/30') IN Dsp32,
		 P3 VALUES <= ('2006/09/30') IN Dsp33
		) ;

Data page allocation for each partition:

Partition	Dbspaces	Columns
P1	Dsp31	c1, c2, c4, c5
P1	Dsp11	c3
P1	Dsp21	c6

P2	Dsp32	c1, c2, c4, c5
P2	Dsp12	c3
P2	Dsp22	c6

P3	Dsp33	c1, c2, c4, c5, c6
P3	Dsp13	c3

P1, P2, P3	Dsp1	lookup store of c1 and other shared data

P1, P2, P3	Dsp2	primary key (HG for c5)

Example 5 – Create a HASH partitioned (table tbl42) that includes a PRIMARY KEY (column c1) and a HASH PARTITION KEY (columns c4 and c3).

CREATE TABLE tbl42 (
    c1 BIGINT NOT NULL,
    c2 CHAR(2) IQ UNIQUE(50),
    c3 DATE IQ UNIQUE(36524),
    c4 VARCHAR(200),
  PRIMARY KEY (c1)
    ) 
  PARTITION BY HASH ( c4, c3 )

Example 6 – Create a hash-ranged partitioned table with a PRIMARY KEY (column c1), a hash partition key (columns c4 and c2) and a range subpartition key (column c3).

CREATE TABLE tbl42 (
    c1 BIGINT NOT NULL,
    c2 CHAR(2) IQ UNIQUE(50),
    c3 DATE,
    c4 VARCHAR(200),
  PRIMARY KEY (c1)) IN Dsp1
  
  PARTITION BY HASH ( c4, c2 )
  SUBPARTITION BY RANGE ( c3 )
  ( P1 VALUES <= (2011/03/31) IN Dsp31,
    P2 VALUES <= (2011/06/30) IN Dsp32,
    P3 VALUES <= (2011/09/30) IN Dsp33) ;

Example 7 – Create a table for a library database to hold information on borrowed books:

CREATE TABLE borrowed_book (
date_borrowed DATE NOT NULL,
date_returned DATE,
book       CHAR(20)
           REFERENCES library_books (isbn),
CHECK( date_returned >= date_borrowed )
)

Example 8 – Create table t1 at the remote server SERVER_A and create a proxy table named t1 that is mapped to the remote table:
```
CREATE TABLE t1
( a  INT,
  b  CHAR(10))
AT 'SERVER_A.db1.joe.t1'
```
Example 9 – Create table tab1 that contains a column c1 with a default value of the special constant LAST USER:
```
CREATE TABLE tab1(c1 CHAR(20) DEFAULT LAST USER)
```
Example 10 – Create a local temporary table tab1 that contains a column c1:
```
CREATE LOCAL TEMPORARY TABLE tab1(c1 int) IN IQ_SYSTEM_TEMP
```
The example creates tab1 in the IQ_SYSTEM_TEMP dbspace in the following cases:
- DQP_ENABLED logical server policy option is set ON but there are no read-write files in IQ_SHARED_TEMP
- DQP_ENABLED option is OFF, TEMP_DATA_IN_SHARED_TEMP logical server policy option is ON, but there are no read-write files in IQ_SHARED_TEMP
- Both the DQP_ENABLED option and the TEMP_DATA_IN_SHARED_TEMP option are set OFF
The example creates the same table tab1 in the IQ_SHARED_TEMP dbspace in the following cases:
- DQP_ENABLED is ON and there are read-write files in IQ_SHARED_TEMP
- DQP_ENABLED is OFF, TEMP_DATA_IN_SHARED_TEMP is ON, and there are read-write files in IQ_SHARED_TEMP
Example 11 – Create a table tab1 that is enabled to use row-level versioning, and real-time storage in the in-memory RLV store.
```
CREATE TABLE tab1 ( c1 INT, c2 CHAR(25) ) ENABLE RLV STORE
```

Usage

You can create a table for another user by specifying an owner name. If GLOBAL TEMPORARY or LOCAL TEMPORARY is not specified, the table is referred to as a base table. Otherwise, the table is a temporary table.

A created global temporary table exists in the database like a base table and remains in the database until it is explicitly removed by a DROP TABLE statement. The rows in a temporary table are visible only to the connection that inserted the rows. Multiple connections from the same or different applications can use the same temporary table at the same time and each connection sees only its own rows. A given connection inherits the schema of a global temporary table as it exists when the connection first refers to the table. The rows of a temporary table are deleted when the connection ends.

When you create a local temporary table, omit the owner specification. If you specify an owner when creating a temporary table, for example, CREATE TABLE dbo.#temp(col1 int), a base table is incorrectly created.

An attempt to create a base table or a global temporary table will fail, if a local temporary table of the same name exists on that connection, as the new table cannot be uniquely identified by owner.table.

You can, however, create a local temporary table with the same name as an existing base table or global temporary table. References to the table name access the local temporary table, as local temporary tables are resolved first.

For example, consider this sequence:

CREATE TABLE t1 (c1 int);
INSERT t1 VALUES (9);

CREATE LOCAL TEMPORARY TABLE t1 (c1 int);
INSERT t1 VALUES (8);

SELECT * FROM t1;

The result returned is 8. Any reference to t1 refers to the local temporary table t1 until the local temporary table is dropped by the connection.

In a procedure, use the CREATE LOCAL TEMPORARY TABLE statement, instead of the DECLARE LOCAL TEMPORARY TABLE statement, when you want to create a table that persists after the procedure completes. Local temporary tables created using the CREATE LOCAL TEMPORARY TABLE statement remain until they are either explicitly dropped, or until the connection closes.

Local temporary tables created in IF statements using CREATE LOCAL TEMPORARY TABLE also persist after the IF statement completes.

SAP Sybase IQ does not support the CREATE TABLE ENCRYPTED clause for table-level encryption of SAP Sybase IQ tables. However, the CREATE TABLE ENCRYPTED clause is supported for SQL Anywhere tables in an SAP Sybase IQ database.

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

{ ENABLE | DISABLE } RLV STORE — Registers this table with the RLV store for real-time in-memory updates. This value overrides the value of the database option BASE_TABLES_IN_RLV. Requires the CREATE TABLE system privilege and CREATE permissions on the RLV store dbspace to set this value to ENABLE.

Note: The { ENABLE | DISABLE } RLV STORE clause is not supported for IQ temporary tables.

WITH NULLS NOT DISTINCT clause can only be specified if you are declaring the index to be UNIQUE and allows you to specify that NULLs in index keys are not unique. See the UNIQUE clause for more information.

IN — Specifies in which database file (dbspace) the table is to be created. 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

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

For more information about dbspaces, see CREATE DBSPACE Statement .

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

NOT TRANSACTIONAL — Allowed only for temporary tables. A table created using NOT TRANSACTIONAL is not affected by either COMMIT or ROLLBACK.

Note: The NOT TRANSACTIONAL clause is not supported for IQ temporary tables.

The NOT TRANSACTIONAL clause provides performance improvements in some circumstances because operations on nontransactional temporary tables do not cause entries to be made in the rollback log. For example, NOT TRANSACTIONAL might be useful if procedures that use the temporary table are called repeatedly with no intervening COMMIT or ROLLBACK statements.

The parenthesized list following the CREATE TABLE statement can contain these clauses in any order:

AT — Used to create a table at the remote location specified by location-string. The local table that is created is a proxy table that maps to the remote location. Tables used as proxy tables must have names of 30 characters or less. The AT clause supports the semicolon (;) as a delimiter. If a semicolon is present anywhere in the location-string, 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 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.

For example, in a simplex environment, if you try to create proxy table proxy_e which refers to base table Employees defined on the same node, the CREATE TABLE ... AT statement is rejected with an error message. In a multiplex environment, the CREATE TABLE .... AT statement is rejected if you create proxy table proxy_e from any node (coordinator or secondary) that refers to remote table Employees defined within a multiplex.

column-definition — Defines a column in the table. 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. If NOT NULL is specified, or if the column is in a UNIQUE or PRIMARY KEY constraint, the column cannot contain any NULL values. You can create up to 45,000 columns; however, there might be performance penalties with more than 10,000 columns in a table. The limit on the number of columns per table that allow NULLs is approximately 8*(database-page-size - 30).

DEFAULT default-value—When defining a column for a table, you can specify a default value for the column using the DEFAULT keyword in the CREATE TABLE (and ALTER TABLE) statement. If a DEFAULT value is specified for a column, this DEFAULT value is used as the value of the column in any INSERT (or LOAD) statement that does not specify a value for the column.
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 Adaptive Server Enterprise. 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. The database option IDENTITY_INSERT must be set to the table name to perform an insert into an IDENTITY/AUTOINCREMENT column.

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 Sybase 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 constraint — 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 constraint—Is 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 constraint — 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.

Note: You cannot create foreign-key constraints on local temporary tables. Global temporary tables must be created with ON COMMIT PRESERVE ROWS.
CHECK constraint — 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 Sybase 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 constraint — 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 Sybase 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.

Integrity Constraints

UNIQUE or UNIQUE (column-name, … )—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.

There is a difference between a unique constraint and a unique index. Columns of a unique index are allowed to be NULL, while columns in a unique constraint are not. A foreign key can reference either a primary key or a column with a unique constraint, but not a unique index, because it can include multiple instances of NULL.

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 Sybase 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 Sybase 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 Sybase 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:

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.
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 Sybase 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 Sybase IQ.

Note: If possible, do not define referential integrity foreign key-primary key relationships in SAP Sybase 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.

TABLE PARTITIONING

Table partitioning can improve performance by dividing 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 Sybase IQ supports several table partitioning schemes:

hash-partitions
range-partitions
composite-partitions

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

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 clause — 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 — In the partition-decl, specifies the dbspace 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 Sybase 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 Sybase IQ supports only single column partition keys, so any NULL in the partition key distributes the row to the first partition.

PARTITION BY HASH — Hash partitioning 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.

Hash-Range Partitions — Hash-range partitioning is a composite partitioning scheme that 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.

Side Effects

Automatic commit

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
These are vendor extensions:
- The { IN | ON } dbspace-name clause
- The ON COMMIT clause
- Some of the default values
Sybase—Supported by Adaptive Server Enterprise, with some differences.
- Temporary tables— You can create a temporary table by preceding the table name in a CREATE TABLE statement with a pound sign (#). These temporary tables are SAP Sybase IQ declared temporary tables, which are available only in the current connection. For information about declared temporary tables, see DECLARE LOCAL TEMPORARY TABLE Statement.
- Physical placement—Physical placement of a table is carried out differently in SAP Sybase IQ and in Adaptive Server Enterprise. The ON segment-name clause supported by Adaptive Server Enterprise is supported in SAP Sybase IQ, but segment-name refers to an IQ dbspace.
- Constraints—SAP Sybase IQ does not support named constraints or named defaults, but does support user-defined data types that allow constraint and default definitions to be encapsulated in the data type definition. It also supports explicit defaults and CHECK conditions in the CREATE TABLE statement.
- NULL default—By default, columns in Adaptive Server Enterprise default to NOT NULL, whereas in SAP Sybase IQ the default setting is NULL, to allow NULL values. This setting can be controlled using the ALLOW_NULLS_BY_DEFAULT option. See ALLOW_NULLS_BY_DEFAULT Option [TSQL]. To make your data definition statements transferable, explicitly specify NULL or NOT NULL.

Permissions

Table Type	Privileges Required
Base table in the IQ main store	Table owned by self – Requires CREATE privilege on the dbspace where the table is created. Also requires one of: CREATE TABLE system privilege. CREATE ANY OBJECT system privilege. Table owned by any user – Requires CREATE privilege on the dbspace where the table is created. Also requires one of: CREATE ANY TABLE system privilege. CREATE ANY OBJECT system privilege.
Global temporary table	Table owned by self – Requires one of: CREATE TABLE system privilege. CREATE ANY OBJECT system privilege. Table owned by any user – Requires one of: CREATE ANY TABLE system privilege. CREATE ANY OBJECT system privilege.
Proxy table	Table owned by self – Requires one of: CREATE PROXY TABLE system privilege. CREATE ANY TABLE system privilege. CREATE ANY OBJECT system privilege. Table owned by any user – Requires one of: CREATE ANY TABLE system privilege. CREATE ANY OBJECT system privilege.

Table Type

Privileges Required

Base table in the IQ main store

Table owned by self – Requires CREATE privilege on the dbspace where the table is created. Also requires one of:

CREATE TABLE system privilege.
CREATE ANY OBJECT system privilege.

Table owned by any user – Requires CREATE privilege on the dbspace where the table is created. Also requires one of:

CREATE ANY TABLE system privilege.
CREATE ANY OBJECT system privilege.

Global temporary table

Table owned by self – Requires one of:

CREATE TABLE system privilege.
CREATE ANY OBJECT system privilege.

Table owned by any user – Requires one of:

CREATE ANY TABLE system privilege.
CREATE ANY OBJECT system privilege.

Proxy table

Table owned by self – Requires one of:

CREATE PROXY TABLE system privilege.
CREATE ANY TABLE system privilege.
CREATE ANY OBJECT system privilege.

Table owned by any user – Requires one of:

CREATE ANY TABLE system privilege.
CREATE ANY OBJECT system privilege.