CREATE TABLE statement

Description

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

Syntax

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

Parameters

Examples

Example 1

• 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) )

• 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 ) )

• 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'
  

• 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) LAST USER)
  

Usage

You can create a table for another user by specifying an owner name. If GLOBAL 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.

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

Also, you cannot update a base table that is part of any join index. If you do, you see the following error message:

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

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 the following 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, the following statement maps the table proxy_a to the Adaptive Server 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.

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 Product (
	product_num integer UNIQUE
)
CREATE TABLE Product (
	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.

Automatic commit.

Standards