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

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

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

• Creates a table named t1 at the remote server SERVER_A and creates 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'
  

Usage

The CREATE TABLE statement creates a new table. A table can be created 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 is a table that 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 only visible 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 will only see 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.

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

The NOT TRANSACTIONAL clause provides performance improvements in some circumstances because operations on non-transactional temporary tables do not cause entries to be made in the rollback log. For example, NOT TRANSACTIONAL may 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.

When the MINIMIZE_STORAGE option is ON (the default for new databases is OFF), it is equivalent to specifying IQ UNIQUE 255 for every newly created column, and there is no need to specify IQ UNIQUE except for columns with more than 65536 unique values.

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, then the primary table columns will be the columns in the table's primary key. If foreign key column names are not specified then the foreign key columns will 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 null(s) in a null allows foreign key column.

Any foreign key column not explicitly defined will automatically be 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.

The role-name is the name of the foreign key. The main function of the 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, then 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 three-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 will be sent to the remote server if the server supports it.

Automatic commit.

Standards