Contents Index CREATE SYNCHRONIZATION USER statement [MobiLink] CREATE TRIGGER statement

ASA SQL Reference
  SQL Statements

CREATE TABLE statement

Description 

Use this statement to create a new table in the database and, optionally, to create a table on a remote server.

Syntax 

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

column-definition :
column-name data-type [ NOT NULL ]
 [ DEFAULT default-value ] [ column-constraint ... ]

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

special-value:
  CURRENT { DATE | TIME | TIMESTAMP
     | UTC TIMESTAMP | USER | PUBLISHER }
| USER

column-constraint :
[ CONSTRAINT constraint-name ] {
     UNIQUE
  | PRIMARY KEY [ CLUSTERED ]
  | REFERENCES table-name
        [ ( column-name ) ] [ actions ] [ CLUSTERED ]
}
| [ CONSTRAINT constraint-name ] CHECK ( condition )
| COMPUTE ( expression )

table-constraint :
[ CONSTRAINT constraint-name ] {
     UNIQUE ( column-name, ... )
   | PRIMARY KEY [ CLUSTERED ] ( column-name, ... )
   | CHECK ( condition )
   | foreign-key-constraint
}

foreign-key-constraint :
[ NOT NULL ] FOREIGN KEY [ role-name ] [ (column-name, ... ) ]
REFERENCES table-name [ (column-name, ... ) ] [ CLUSTERED ]
[ actions ] [ CHECK ON COMMIT ]

action :
ON { UPDATE | DELETE }
...{ CASCADE | SET NULL | SET DEFAULT | RESTRICT }

location-string :
  remote-server-name.[db-name].[owner].object-name
| remote-server-name;[db-name];[owner];object-name

pctfree : PCTFREE percent-free-space

percent-free-space : integer

Parameters 

PCTFREE    Specifies the percentage of free space you want to reserve for each table page. The free space is used if rows increase in size when the data is updated. If there is no free space in a table page, every increase in the size of a row on that page requires the row to be split across multiple table pages, causing row fragmentation and possible performance degradation.

The value percent-free-space is an integer between 0 and 100. The former specifies that no free space is to be left on each page—each page is to be fully packed. A high value causes each row to be inserted into a page by itself. If PCTFREE is not set, 200 bytes are reserved in each page.

The value for PCTFREE is stored in the SYSATTRIBUTE system table.

For more information, see SYSATTRIBUTE system table.

IN clause    The IN clause specifies the dbspace in which the table is to be created. If the table is a GLOBAL TEMPORARY table, the IN clause is ignored.

For more information about dbspaces, see CREATE DBSPACE statement.

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

NOT TRANSACTIONAL    The NOT TRANSACTIONAL clause is allowed only for temporary tables. A table created using NOT TRANSACTIONAL is not affected by either COMMIT or ROLLBACK.

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.

AT clause    Create a remote table on a different server specified by location-string and also a proxy table on the current database that maps to the remote table. The AT clause supports the semicolon (;) as a field delimiter in location-string. If no semicolon is present, a period is the field delimiter. This allows filenames and extensions to be used in the database and owner fields.

For example, the following statement maps the table a1 to the MS Access file mydbfile.mdb:

CREATE TABLE a1
AT 'access;d:\mydbfile.mdb;;a1'

For information on remote servers, see CREATE SERVER statement. For information on proxy tables, see CREATE EXISTING TABLE statement and Specifying proxy table locations.

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.

The COMPUTE clause is ignored for remote tables.

column-definition    Define a column in the table. The following are part of column definitions.

table-constraint    A table constraint restricts the values that one or more columns in the table can hold.

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

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.

Constraints include the following:

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 specified, the table is a temporary table. Otherwise, the table is a base table.

The definition of a temporary table exists in the database, like that of 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 will see only its own rows. The rows of a temporary table for a connection are deleted when the connection ends.

Permissions 

Must have RESOURCE authority.

Must have DBA authority to create a table for another user.

The AT clause to create proxy tables is not supported on Windows CE.

Side effects 

Automatic commit.

See also 

ALTER TABLE statement

CREATE DBSPACE statement

CREATE EXISTING TABLE statement

DECLARE LOCAL TEMPORARY TABLE statement

DROP statement

Special values

SQL Data Types

Creating tables

Standards and compatibility 
Example 

The following example creates a table for a library database to hold book information.

CREATE TABLE library_books (
-- NOT NULL is assumed for primary key columns
isbn CHAR(20)      PRIMARY KEY,
copyright_date     DATE,
title              CHAR(100),
author             CHAR(50),
-- column(s) corresponding to primary key of room
-- are created automatically
FOREIGN KEY location REFERENCES room
)

The following example creates a table for a library database to hold information on borrowed books. The default value for date_borrowed indicates that the book is borrowed on the day the entry is made. The date_returned column is NULL until the book is returned.

CREATE TABLE borrowed_book (
date_borrowed DATE NOT NULL DEFAULT CURRENT DATE,
date_returned       DATE,
book                CHAR(20)
                    REFERENCES library_books (isbn),
-- The check condition is UNKNOWN until
-- the book is returned, which is allowed
CHECK( date_returned >= date_borrowed )
)

The following example creates tables for a sales database to hold order and order item information.

CREATE TABLE Orders (
   order_num INTEGER NOT NULL PRIMARY KEY,
   date_ordered DATE,
   name CHAR(80)
);
CREATE TABLE Order_item (
   order_num        INTEGER NOT NULL,
   item_num         SMALLINT NOT NULL,
   PRIMARY KEY (order_num, item_num),
   -- When an order is deleted, delete all of its
   -- items.
   FOREIGN KEY (order_num)
   REFERENCES Orders (order_num)
   ON DELETE CASCADE
)

The following example 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'

Contents Index CREATE SYNCHRONIZATION USER statement [MobiLink] CREATE TRIGGER statement