Use this statement to create a new table in the database and, optionally, to create a table on a remote server.
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 | IDENTITY ] [ 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 } ] [ action-list ] [ 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 } ] [ action-list ] [ CHECK ON COMMIT ] [ CLUSTERED ] [ FOR OLAP WORKLOAD ]
action-list : [ ON UPDATE action ] [ ON DELETE action ]
action : 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
IN clause The IN clause specifies the dbspace in which the table is to be created.
If you are creating a base table, and do not specify the IN clause, the table is created in the dbspace specified by the default_dbspace option. Otherwise, the table is created in the SYSTEM dbspace.
Temporary tables can only be created in the TEMPORARY dbspace. If you are creating a GLOBAL TEMPORARY table, and specify IN SYSTEM, the table is created in the temporary dbspace. If you specify a user-defined dbspace, an error is returned.
For more information about dbspaces, see:
ENCRYPTED clause The encrypted clause specifies that the table should be encrypted. 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 clause 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 about 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 about remote servers, see CREATE SERVER statement. For information about proxy tables, see CREATE EXISTING TABLE statement and Specifying proxy table locations.
Windows Mobile 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 about 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.
column-name The column name is an identifier. Two columns in the same table cannot have the same name. See Identifiers.
data-type The type of data stored in the column. See SQL data types.
COMPRESSED Compress the column. For example, the following statement creates a table, t, with two columns: filename and contents. The contents column is LONG BINARY and is compressed:
CREATE TABLE t ( filename VARCHAR(255), contents LONG BINARY COMPRESSED ); |
INLINE and PREFIX clause The INLINE and PREFIX clauses are for use with storing BLOBs (character or binary data types only). Use the INLINE clause to specify the maximum BLOB size, in bytes, to store in the column. BLOBs that exceed the INLINE value are stored outside of the row in table extension pages. Use the PREFIX clause to specify how many bytes of the BLOB to duplicate and store with the row. Prefix data can improve performance when processing requests that need only the prefix bytes of a BLOB.
The prefix data for a compressed column is stored uncompressed, so if all of the data required to satisfy a request is stored in the prefix, no decompression is necessary.
If neither INLINE nor PREFIX is specified, or if USE DEFAULT is specified, default values are applied as follows:
It is strongly recommended that you use the default values unless there are specific circumstances that require a different setting. The default values have been chosen to balance performance and disk space requirements. For example, if you set INLINE to a large value, and all the BLOBs are stored inline, row processing performance may degrade. If you set PREFIX too high, you increase the amount of disk space required to store BLOBs since the prefix data is a duplicate of a portion of the BLOB.
If only one of the values is specified, the other value is automatically set to the largest amount that does not conflict with the specified value. Neither the INLINE nor PREFIX value can exceed the database page size. Also, there is a small amount of overhead reserved in a table page that cannot be used to store row data. Therefore, specifying an INLINE value approximate to the database page size can result in a slightly smaller number of bytes being stored inline.
[ NO ] INDEX clause When storing BLOBs (character or binary types only), use this clause to specify whether to create BLOB indexes. If this clause is not specified, the database server creates the indexes. BLOB indexes can improve performance when random access searches within the BLOBs are required. However, for some types of BLOB values, such as images and multimedia files, BLOB indexing is not required and, in fact, performance can improve if BLOB indexing is turned off. Specify NO INDEX to turn off BLOB indexing for the specified column.
A BLOB index is not the same as a database index. A BLOB index is created to provide faster random access into BLOB data, whereas a database index is created to index values in one or more columns.
NOT NULL clause If NOT NULL is specified, or if the column is in a UNIQUE or PRIMARY KEY constraint, the column cannot contain NULL in any row.
DEFAULT clause For more information about the special-value, see Special values.
If a DEFAULT value is specified, it is used as the value for the column in any INSERT statement that does not specify a value for the column. If no DEFAULT value is specified, it is equivalent to DEFAULT NULL.
Following is a list of possible values for DEFAULT:
Constant expressions Constant expressions that do not reference database objects are allowed in a DEFAULT clause, so functions such as GETDATE or DATEADD can be used. If the expression is not a function or simple value, it must be enclosed in parentheses.
AUTOINCREMENT When using AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type.
On inserts into the table, if a value is not specified for the AUTOINCREMENT column, a unique value larger than any other value in the column is generated. If an INSERT specifies a value for the column that is larger than the current maximum value for the column, that value is inserted and then used as a starting point for subsequent inserts.
Deleting rows does not decrement the AUTOINCREMENT counter. Gaps created by deleting rows can only be filled by explicit assignment when using an insert. After an explicit insert of a column value less than the maximum, subsequent rows without explicit assignment are still automatically incremented with a value of one greater than the previous maximum.
You can find the most recently inserted value of the column by inspecting the @@identity global variable.
AUTOINCREMENT values are maintained as signed 64-bit integers, corresponding to the data type of the max_identity column in the SYSTABCOL system view. When the next value to be generated exceeds the maximum value that can be stored in the column to which the AUTOINCREMENT is assigned, NULL is returned. If the column has been declared to not allow NULLs, as is the case for primary key columns, a SQL error is generated.
IDENTITY The IDENTITY default is a Transact-SQL-compatible alternative to using the AUTOINCREMENT default. In SQL Anywhere, a column defined as IDENTITY is implemented as AUTOINCREMENT. See The special IDENTITY column.
GLOBAL AUTOINCREMENT This default is intended for use when multiple databases are used in a SQL Remote replication or MobiLink synchronization environment.
This option is similar to AUTOINCREMENT, except that the domain is partitioned. Each partition contains the same number of values. You assign each copy of the database a unique global database identification number. SQL Anywhere supplies default values in a database only from the partition uniquely identified by that database's number.
The partition size can be specified in parentheses immediately following the AUTOINCREMENT keyword. The partition size can be any positive integer, although the partition size is generally chosen so that the supply of numbers within any one partition will rarely, if ever, be exhausted.
If the column is of type BIGINT or UNSIGNED BIGINT, the default partition size is 232, = 4294967296; for columns of all other types, the default partition size is 216 = 65536. Since these defaults may be inappropriate, especially if your column is not of type INT or BIGINT, it is best to specify the partition size explicitly.
When using this default, the value of the public option global_database_id in each database must be set to a unique, non-negative integer. This value uniquely identifies the database and indicates from which partition default values are to be assigned. The range of allowed values is np + 1 to p(n + 1), where n is the value of the public option global_database_id and p is the partition size. For example, if you define the partition size to be 1000 and set global_database_id to 3, then the range is from 3001 to 4000.
If the previous value is less than p(n + 1), the next default value is one greater than the previous largest value in the column. If the column contains no values, the first default value is np + 1. Default column values are not affected by values in the column outside of the current partition; that is, by numbers less than np + 1 or greater than p(n + 1). Such values may be present if they have been replicated from another database via MobiLink or SQL Remote.
You can find the most recently inserted value of the column by inspecting the @@identity global variable.
GLOBAL AUTOINCREMENT values are maintained as signed 64-bit integers, corresponding to the data type of the max_identity column in the SYSTABCOL system view. When the supply of values within the partition has been exhausted, NULL is returned. If the column has been declared to not allow NULLs, as is the case for primary key columns, a SQL error is generated. In this case, a new value of global_database_id should be assigned to the database to allow default values to be chosen from another partition. To detect that the supply of unused values is low and handle this condition, create an event of type GlobalAutoincrement. See Understanding events.
Because the public option global_database_id cannot be set to a negative value, the values chosen are always positive. The maximum identification number is restricted only by the column data type and the partition size.
If the public option global_database_id is set to the default value of 2147483647, a NULL value is inserted into the column. If NULL values are not permitted, attempting to insert the row causes an error.
TIMESTAMP clause Provides a way of indicating when each row in the table was last modified. When a column is declared with DEFAULT TIMESTAMP, a default value is provided for inserts, and the value is updated with the current date and time whenever the row is updated.
To provide a default value on insert, but not update the column whenever the row is updated, use DEFAULT CURRENT TIMESTAMP instead of DEFAULT TIMESTAMP.
For more information about timestamp columns, see The special Transact-SQL timestamp column and data type.
Columns declared with DEFAULT TIMESTAMP contain unique values, so that applications can detect near-simultaneous updates to the same row. If the current timestamp value is the same as the last value, it is incremented by the value of the default_timestamp_increment option. See default_timestamp_increment option [database] [MobiLink client].
You can automatically truncate timestamp values in SQL Anywhere based on the default_timestamp_increment option. This is useful for maintaining compatibility with other database software that records less precise timestamp values. See default_timestamp_increment option [database] [MobiLink client].
The global variable @@dbts returns a TIMESTAMP value representing the last value generated for a column using DEFAULT TIMESTAMP. See Global variables.
UTC TIMESTAMP clause The behavior of UTC TIMESTAMP is the same as TIMESTAMP except that a UTC TIMESTAMP value is in Coordinated Universal (UTC) time.
string See Strings.
global-variable See Global variables.
column-constraint and table-constraint clauses 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. There are two classes of constraints that can be created: check constraints, and referential integrity (RI) constraints. Check constraints are used to specify conditions that must be satisfied by values of columns being put into the database. RI constraints establish a relationship between data in different tables that must be maintained in addition to specifying uniqueness requirements for data.
There are three types of RI constraints: primary key, foreign key, and unique constraint. When you create an RI constraint (primary key, foreign key or unique constraint), the database server enforces the constraint by implicitly creating an index on the columns that make up the key of the constraint. The index is created on the key for the constraint as specified. A key consists of an ordered list of columns and a sequencing of values (ASC/DESC) for each column.
Constraints can be specified on columns or tables. Generally speaking, a column constraint is one that refers to one column in a table, while a table constraint can refer to one or more columns in a table.
PRIMARY KEY constraint A primary key uniquely defines each row in the table. Primary keys comprise one or more columns. A table cannot have more than one primary key. In a column-constraint clause, specifying PRIMARY KEY indicates that the column is the primary key for the table. In a table-constraint, you use the PRIMARY KEY clause to specify one or more columns that, when combined in the specified order, make up the primary key for the table.
The ordering of columns in a primary key need not match the respective ordinal numbers of the columns. That is, the columns in a primary key need not have the same physical order in the row. Additionally, you cannot specify duplicate column names.
When you create a primary key, an index for the key is automatically created. You can specify the sequencing of values in the index by specifying ASC (ascending) or DESC (descending) for each column. You can also specify whether to cluster the index, using the CLUSTERED keyword. For more information about the CLUSTERED option and clustered indexes, see Using clustered indexes.
Columns included in primary keys cannot allow NULL. Each row in the table has a unique primary key value.
It is recommended that you do not use approximate data types such as FLOAT and DOUBLE for primary keys. Approximate numeric data types are subject to rounding errors after arithmetic operations.
Foreign key A foreign key restricts the values for a set of columns to match the values in a primary key or a unique constraint of another table (the primary table). For example, a foreign key constraint could be used to ensure that a customer number in an invoice table corresponds to a customer number in the Customers table.
If a specified foreign key column does not exist in the table, the column is 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 of the same table must be explicitly created, before the creation of the key.
Foreign key column names are paired with primary key column names according to position in the two lists in a one-to-one manner. If the primary table column names are not specified in a FOREIGN KEY table constraint, then the primary key columns are used. If foreign key column names are not specified, then the foreign key columns are give the same names as the columns in the primary table.
The foreign key column order does not need to reflect the order of columns in the table.
Duplicate column names are not allowed in the foreign key specification.
The default action is RESTRICT if no action is specified for a particular operation (UPDATE or DELETE).
When you create a foreign key, an index for the key is automatically created. You can specify the sequencing of values in the index by specifying ASC (ascending) or DESC (descending) for each column. You can also specify whether to cluster the index, using the CLUSTERED keyword. For more information about the CLUSTERED option and clustered indexes, see Using clustered indexes.
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.
NOT NULL option Disallow NULLs in the foreign key columns. A NULL in a foreign key means that no row in the primary table corresponds to this row in the foreign table.
role-name clause 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:
REFERENCES clause A foreign key constraint can be implemented using a REFERENCES column constraint (single column only) or a FOREIGN KEY table constraint, in which case the constraint can specify one or more columns. If you specify column-name in a REFERENCES column constraint, it must be a column in the primary table, must be subject to a unique constraint or primary key constraint, and that constraint must consist of only that one column. If you do not specify column-name, the foreign key column references the single primary key column of the primary table.
MATCH clause The MATCH clause allows you to control what is considered a match when using a multi-column foreign key. It also allows you to specify uniqueness for the key, thereby eliminating the need to declare uniqueness separately. Following is a list match types you can specify:
UNIQUE option The referencing table can have only one match for non-NULL key values (keys with at least one non-NULL column value are implicitly unique).
SIMPLE option A match occurs for a row in the referencing table if at least one column in the key is NULL, or all the column values match the corresponding column values present in a row of the referenced table.
FULL option A match occurs for a row in the referencing table if all column values in the key are NULL, or if all of the column values match the values present in a row of the referenced table.
SIMPLE UNIQUE option A match occurs if the criteria for both SIMPLE and UNIQUE are met.
FULL UNIQUE option A match occurs if the criteria for both FULL and UNIQUE are met.
UNIQUE constraint In a column-constraint clause, a UNIQUE constraint specifies that the values in the column must be unique. In a table-constraint clause, the UNIQUE constraint identifies one or more columns that uniquely identify each row in the table. No two rows in the table can have the same values in all the named column(s). A table can have more than one UNIQUE constraint.
A UNIQUE constraint is not the same as a unique index. Columns of a unique index are allowed to be NULL, while columns in a UNIQUE constraint are not. Also, a foreign key can reference either a primary key or a UNIQUE constraint, but cannot reference a unique index since a unique index can include multiple instances of NULL.
Columns in a UNIQUE constraint can be specified in any order. Additionally, you can specify the sequencing of values in the corresponding index that is automatically created, by specifying ASC (ascending) or DESC (descending) for each column. You cannot specify duplicate column names, however.
It is recommended that you do not use approximate data types such as FLOAT and DOUBLE for columns with unique constraints. Approximate numeric data types are subject to rounding errors after arithmetic operations.
You can also specify whether to cluster the constraint, using the CLUSTERED keyword. For more information about the CLUSTERED option, see Using clustered indexes.
For information about unique indexes, see CREATE INDEX statement.
CHECK constraint This allows arbitrary conditions to be verified. For example, a CHECK constraint could be used to ensure that a column called Sex only contains the values M or F.
No row in a table is allowed to violate a CHECK constraint. If an INSERT or UPDATE statement would cause a row to violate the constraint, the operation is not permitted and the effects of the statement are undone. The change is rejected only if a CHECK constraint condition evaluates to FALSE, and the change is allowed if a CHECK constraint condition evaluates to TRUE or UNKNOWN.
For more information about TRUE, FALSE, and UNKNOWN conditions, see NULL value, and Search conditions.
COMPUTE clause The COMPUTE clause is only for use in a column-constraint clause. When a column is created using a COMPUTE clause, its value in any row is the value of the supplied expression. Columns created with this constraint are read-only columns for applications: the value is changed by the database server when the expression is evaluated. The COMPUTE expression cannot return a non-deterministic value. For example, it cannot include a special value such as CURRENT TIMESTAMP, or a non-deterministic function.
The COMPUTE clause is ignored for remote tables.
Any UPDATE statement that attempts to change the value of a computed column fires any triggers associated with the column.
CHECK ON COMMIT option The CHECK ON COMMIT option overrides the wait_for_commit database option, and causes the database server to wait for a COMMIT before checking RESTRICT actions on a foreign key. The CHECK ON COMMIT option does not delay CASCADE, SET NULL, or SET DEFAULT actions.
If you use CHECK ON COMMIT without specifying any actions, then RESTRICT is implied as an action for UPDATE and DELETE.
FOR OLAP WORKLOAD option When you specify FOR OLAP WORKLOAD in the REFERENCES clause of a foreign key definition, the database server performs certain optimizations and gather statistics on the key to help improve performance for OLAP workloads, particularly when the optimization_workload is set to OLAP. See optimization_workload option [database].
For more information about OLAP, see OLAP support.
PCTFREE clause 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, or is later dropped, the default PCTFREE value is applied according to the database page size (200 bytes for a 4 KB (and up) page size). The value for PCTFREE is stored in the ISYSTAB system table.
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].
Must have RESOURCE authority.
Must have DBA authority to create a table for another user.
Automatic commit.
SQL/2003 Core feature.
The following are vendor extensions:
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'; |
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |