Reference Manual
CHAPTER 9. SQL Statements
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 [ column-constraint ... ] | table-constraint }, ... )
... [ { IN | ON } dbspace-name ]
... [ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ AT location-string ]
column-definition:
column-name data-type [ NOT NULL ] [ DEFAULT default-value ]
column-constraint:
UNIQUE
| PRIMARY KEY
| REFERENCES table-name [( column-name )] [ actions ]
| CHECK ( condition )
| COMPUTE ( expression )
default-value:
string
| global variable
| number
| AUTOINCREMENT
| CURRENT DATE
| CURRENT TIME
| CURRENT TIMESTAMP
| NULL
| USER
| ( constant-expression )
table-constraint:
UNIQUE ( column-name, ... )
| PRIMARY KEY ( column-name, ... )
| CHECK ( condition )
| foreign-key-constraint
foreign-key-constraint:
[NOT NULL] FOREIGN KEY [role-name] [(column-name, ... )]
... REFERENCES table-name [(column-name, ... )]
... [ 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
Must have RESOURCE authority. To create a table for another user, you must have DBA authority.
The AT clause to create proxy tables is supported on Windows 95 and Windows NT only.
Automatic commit.
CREATE EXISTING TABLE statement
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.
A created 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 will see only its own rows. The rows of a temporary table are deleted when the connection ends.
IN clause The IN clause specifies in which database file (dbspace) 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.
The parenthesized list following the CREATE TABLE statement can contain the following clauses in any order:
AT clause The AT clause is used to create a table at the remote location specified by location-string. The local table that is created is a proxy table that maps to the remote location. The AT clause supports the semicolon (;) as a delimiter. If a semicolon is present anywhere in the location-string string, the semicolon is the field delimiter. 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'
column-name data-type [ NOT NULL ] [ DEFAULT default-value ] [ column-constraint ] Define a column in the table. Allowable data types are described in SQL Data Types. Two columns in the same table cannot have the same name.
If NOT NULL is specified, or if the column is in a UNIQUE or PRIMARY KEY constraint, the column cannot contain any NULL values. If a DEFAULT value is specified, it will be used as the value for the column in any INSERT statement that does not specify a value for the column. If no DEFAULT is specified, it is equivalent to DEFAULT NULL.
When using DEFAULT AUTOINCREMENT, the data type must be one of the integer data types, or FLOAT, or DOUBLE. On INSERTs into the table, if a value is not specified for the autoincrement column, a unique value is generated. If a value is specified, it will be used. If the value is larger than the current maximum value for the column, that value will be 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 row number less then the maximum, subsequent rows without explicit assignment are still autoincremented with a value of one greater than the previous maximum.
The next value to be used for each column is stored as a long integer (4 bytes). Using values greater than (2**31 - 1), that is, large double or numeric values, may cause wraparound to negative values, and AUTOINCREMENT should not be used in such cases.
For performance reasons, it is highly recommended that DEFAULT AUTOINCREMENT be used only with columns defined as a PRIMARY KEY, with a UNIQUE constraint; or the first column of an index. This allows the maximum value to be found at startup time without scanning the entire table.
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.
Column constraints are abbreviations for the corresponding table constraints. For example, the following are equivalent:
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.
table-constraint Table constraints help ensure the integrity of data in the database. There are four types of integrity constraints:
UNIQUE constraint Identifies one or more columns that uniquely identify each row in the table.
PRIMARY KEY constraint This is the same as a unique constraint, except that a table can have only one primary key constraint. The primary key usually identifies the best identifier for a row. For example, the customer number might be the primary key for the customer table.
FOREIGN KEY constraint This restricts the values for a set of columns to match the values in a primary key or uniqueness constraint of another 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 customer table.
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 is allowed to fail the condition. If an INSERT or UPDATE statement would cause a row to fail the condition, the operation is not permitted and the effects of the statement are undone.
When is the change rejected? |
COMPUTE 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.
Any UPDATE statement that attempts to change the value of a computed column does, however, fire any triggers associated with the column.
The Compute constraint is particularly useful when designing databases using Java class data types. For more information, see Using computed columns with Java classes.
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.)
column-definition UNIQUE or UNIQUE ( column-name, ... ) No two rows in the table can have the same values in all the named column(s). A table may have more than one unique constraint.
Unique constraint versus unique index |
column-definition PRIMARY KEY, or PRIMARY KEY ( column-name, ... ) The primary key for the table will consist of the listed column(s), none of which can contain any NULL values. Each row in the table has a unique primary key value. A table can have only one PRIMARY KEY.
When PRIMARY KEY is followed by a list of columns, the primary key includes the columns in the order in which they are defined in the original CREATE TABLE statement, not the order in which they are listed.
column-definition REFERENCES primary-table-name
[(primary-column-name)] The column is a foreign key for the primary key or a unique constraint in the primary table. Normally, a foreign key would be for a primary key rather than a unique constraint. If a primary column name is specified, it must match a column in the primary table which is subject to a unique constraint or primary key constraint, and that constraint must consist of only that one column. Otherwise the foreign key references the primary key of the second table.
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 ] FOREIGN KEY [role-name] [(...)] REFERENCES primary-table-name [(...)] The table contains a foreign key for the primary key or a unique constraint in another table. Normally, a foreign key would be for a primary key rather than a unique constraint. (In this description, this other table will be called the primary table.)
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.
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 of the same table must be explicitly created.
A NULL foreign key means that no row in the primary table corresponds to this row in the foreign table.
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:
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.
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 either an ON UPDATE clause, an ON DELETE clause, or both, followed by one of the following actions:
CASCADE When used with ON UPDATE, updates the corresponding foreign keys to match the new primary key value. When used with ON DELETE, deletes the rows from the foreign table that match the deleted primary key.
SET NULL Sets to NULL all the foreign key values that correspond to the updated or deleted primary key.
SET DEFAULT Sets foreign key values that match the updated or deleted primary key value to values specified on the DEFAULT clause of each foreign key column.
RESTRICT Generates an error if an attempt is made to update or delete a primary key value while there are corresponding foreign keys elsewhere in the database. This is the default action.
The CHECK ON COMMIT clause causes the database to wait for a COMMIT before checking the referential integrity of inserts and RESTRICT actions on this foreign key, overriding the setting of the WAIT_FOR_COMMIT database option. The CHECK ON COMMIT clause does not delay CASCADE, SET NULL, or SET DEFAULT actions.
If you use CHECK ON COMMIT with out specifying any actions, then RESTRICT is implied as an action for UPDATE and DELETE.
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.
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.
The COMPUTE clause is not supported for remote tables and will be ignored. You may however perform a CREATE EXISTING TABLE against remote tables that contain COMPUTE clause.
SQL/92 Entry level feature.
The following are vendor extensions:
The { IN | ON } dbspace-name clause.
The ON COMMIT clause
Some of the default values.
Sybase Supported by Adaptive Server Enterprise, with some differences.
Temporary tables You can create a temporary table by preceding the table name in a CREATE TABLE statement with a pound sign (#). In Adaptive Server Anywhere, these are declared temporary tables, which are available only in the current connection. For information, see DECLARE LOCAL TEMPORARY TABLE statement.
Physical placement Physical placement of a table is carried out differently in Adaptive Server Anywhere and in Adaptive Server Enterprise. The ON segment-name clause supported by Adaptive Server Enterprise is supported in Adaptive Server Anywhere, but segment-name refers to a dbspace name.
Constraints Adaptive Server Anywhere does not support named constraints or named defaults, but does support user-defined data types which allow constraint and default definitions to be encapsulated in the data type definition. It also supports explicit defaults and CHECK conditions in the CREATE TABLE statement.
NULL default By default, columns in Adaptive Server Enterprise default to NOT NULL, whereas in Adaptive Server Anywhere the default setting is NULL, to allow NULL values. This setting can be controlled using the allow_nulls_by_default option. For information on this option, see ALLOW_NULLS_BY_DEFAULT option. You should explicitly specify NULL or NOT NULL to make your data definition statements transferable.
Create 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 -- will be created FOREIGN KEY location REFERENCES room )
Create a table for a library database to hold information on borrowed books.
CREATE TABLE borrowed_book (
-- Default on insert is that book is borrowed today
date_borrowed DATE NOT NULL DEFAULT CURRENT DATE,
-- date_returned will be NULL until the book is returned
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 )
)
Create 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 )
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'