CREATE TABLE statement

Description

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

Syntax

CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE
 [ owner. ]table-name
… ( column-definition [ column-constraint ] … 
[ , column-definition [ column-constraint ] …]
[ , table-constraint ] … )
…[ IN dbspace-name ]
…[ ON COMMIT { DELETE | PRESERVE } ROWS 
| NOT TRANSACTIONAL ]
[ AT location-string ]
[PARTITION BY range-partitioning-scheme ]

Parameters

Examples

Example 1

Creates 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

Creates 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

Creates a table t1 where partition p1 is adjacent to p2 and partition p2 is adjacent to p3.

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

Example 4

Creates a 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 follows:

Example 5

Creates a table for a library database to hold book information:

CREATE TABLE library_books (
isbn CHAR(20)      PRIMARY KEY IQ UNIQUE (150000),
copyright_date     DATE,
title              CHAR(100),
author             CHAR(50)
)

Example 6

Creates 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 7

Creates a table named t1at 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 8

Creates a table named 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)

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, as, for example, with 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.

You cannot use a temporary table to create a join index.

Do not update a base table that is part of any join index. This is disallowed, and returns this error:

-1000102 Cannot update table %2 because it is defined in one or more join indexes

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

The IN clauses in 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, Sybase IQ creates the object in the dbspace where the table is assigned.

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 COMMITs or ROLLBACKs.

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

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.

If a statement would cause changes to the database that would 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.)

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

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.

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.

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, 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.

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.

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:

If you use CHECK ON COMMIT without specifying any actions, then RESTRICT is implied as an action for DELETE. 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.

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.

The column-name in the partition-key clause specifies the partition key column. Sybase IQ 15.2 supports a single partition key column.

The partition-name in the range-partition-decl clause specifies 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 clause is required.

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, 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 will be 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 nonbinary datatypes to binary datatypes 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. Sybase IQ 15.2 supports only single column partition keys, so any NULL in the partition key distributes the row to the first partition.

Automatic commit.

Standards