Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

CREATE SYNCHRONIZATION USER statement [MobiLink] Next Page

CREATE TABLE statement


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 ]
[ ENCRYPTED ]
[ ON COMMIT { DELETE | PRESERVE } ROWS
| NOT TRANSACTIONAL ]
[ AT location-string ]
[ SHARE BY ALL ]

column-definition :
column-name data-type
[ COMPRESSED ]
[ INLINE { inline-length | USE DEFAULT } ]
[ PREFIX { prefix-length | USE DEFAULT } ]
[ [ NO ] INDEX ]
[ [ 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[ CLUSTERED ]
| PRIMARY KEY [ CLUSTERED ] [ ASC | DESC ]
| REFERENCES table-name [ ( column-name ) ]
[ MATCH [ UNIQUE ] { SIMPLE | FULL } ]
[ actions ] [ CLUSTERED ]
}
| [ CONSTRAINT constraint-name ] CHECK ( condition )
| COMPUTE ( expression )

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

foreign-key-constraint :
[ NOT NULL ] FOREIGN KEY [ role-name ]
[ ( column-name [ ASC | DESC ], ... ) ]
REFERENCES table-name
[ ( column-name, ... ) ]
[ MATCH [ UNIQUE] { SIMPLE | FULL } ]
[ actions ] [ CHECK ON COMMIT ] [ CLUSTERED ] [ FOR OLAP WORKLOAD ]

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

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.

You can also specify the dbspace in which the table is created by setting the default_dbspace option before executing the CREATE TABLE statement. See default_dbspace option [database].

ENCRYPTED    The encrypted clause specifies to encrypt the table. You must enable table encryption when you create a database if you want to encrypt tables. The table is encrypted using the encryption key and algorithm specified at database creation time. See Enabling table encryption.

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. If the SHARE BY ALL clause is specified, either ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL must be specified.

NOT TRANSACTIONAL    The NOT TRANSACTIONAL clause is allowed when creating a global temporary table. A table created using NOT TRANSACTIONAL is not affected by either COMMIT or ROLLBACK. If the SHARE BY ALL clause is specified, either ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL must be specified. For information on the benefits of the NOT TRANSACTIONAL clause, see Working with temporary tables.

AT clause    Create a remote table on a different server specified by location-string, as well as 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 file names and extensions to be used in the database and owner fields.

For example, the following statement maps the table a1 to the Microsoft 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.

Windows CE does not support the AT clause.

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 database server supports primary keys.

SHARE BY ALL clause    Use this clause only when creating global temporary tables to allow the table to be shared by all connections to the database. If the SHARE BY ALL clause is specified, either ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL must be specified.

For information on the characteristics of temporary tables, see Working with temporary tables.

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

Remarks

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.

Tables created by preceding the table name in a CREATE TABLE statement with a pound sign (#) are declared temporary tables, which are available only in the current connection. See DECLARE LOCAL TEMPORARY TABLE statement.

Columns in SQL Anywhere allow NULLs by default. This setting can be controlled using the allow_nulls_by_default database option. See allow_nulls_by_default option [compatibility].

Permissions

Must have RESOURCE authority.

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

Side effects

Automatic commit.

See also
Standards and compatibility
Examples

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