CREATE TABLE statement

User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements
CREATE TABLE statement

Function

To create a new table in the database.

Syntax

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

Parameters

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

     column-constraint:
          UNIQUE
          | PRIMARY KEY
          | REFERENCES table-name [( column-name )] [ actions ]
          | CHECK ( condition )

     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}

Usage

Anywhere.

Permissions

Must have RESOURCE authority. To create a table for another user, you must have DBA authority.

Side effects

Automatic commit.

Description

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.

The IN clause is allowed only for base tables, and is used to specify in which database file the base table will be created. See "CREATE DBSPACE statement" for more information.

A created 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. The rows of a temporary table are deleted when the connection ends.

The ON COMMIT clause is only allowed 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:

column-name data-type [ NOT NULL ] [ DEFAULT default-value ] Define a column in the table. Allowable data types are described in "SQL Anywhere 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 which 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 INTEGER, SMALLINT, 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 doing an explicit insert of a row number less then the maximum, subsequent rows without explicit assignment are 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 only be used with columns defined as a PRIMARY KEY or with a UNIQUE constraint; or columns that are the first column of an index. This will allow the maximum value determined at startup time to be found without scanning the entire table.

Constant expressions that do not reference database objects are allowed in a DEFAULT clause, so that 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.

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 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 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 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 male or female.

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

Integrity constraints

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
There is a difference between a unique constraint and a unique index. Columns in a unique index are allowed to be NULL, while columns in a unique constraint are not. Also, the column referenced by a foreign key can be either a primary key or a column with a unique constraint. Unique indexes cannot be referenced, because they can include multiple NULLs.

column-definition PRIMARY KEY or PRIMARY KEY ( column-name, ... ) The primary key for the table will consist of the listed column(s), and none of the named column(s) can contain any NULL values. SQL Anywhere ensures that each row in the table will have a unique primary key value. A table can have only one PRIMARY KEY.

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.

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 must be explicitly created.

A foreign key can be explicitly created to be NOT NULL. In this case, no row is allowed that is NULL for any column in the key. If NOT NULL is not explicitly stated, the foreign key is still automatically defined as NOT NULL when all the columns in the foreign key do not allow null values at the time the foreign key is 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:

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.
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, update the corresponding foreign keys to match the new primary key value. When used with ON DELETE, deletes the rows from the 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 to the value specified by the column(s) DEFAULT clause, all the foreign key values that match the updated or deleted primary key value.

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 was the only form of referential integrity prior to Watcom SQL Version 4.0 and is the default action if no action is specified.

The CHECK ON COMMIT clause causes the database to wait for a COMMIT before checking the integrity of this foreign key, overriding the setting of the WAIT_FOR_COMMIT database option. CHECK ON COMMIT delays only the RESTRICT referential integrity action; it does not delay any other referential integrity action such as CASCADE or SET NULL. You can have a foreign key declared with CHECK ON COMMIT and a CASCADE or SET NULL referential action. In this case, inserts can be done in the foreign table before the corresponding row is inserted in the primary table as long as the row is inserted before the COMMIT.

If you use the short form of CHECK ON COMMIT then RESTRICT is implied.

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.

column-definition CHECK ( condition ) or CHECK ( condition ) 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?
The change is rejected only if the condition is FALSE; in particular, the change is allowed if the condition is UNKNOWN. (See "NULL value" and "Search conditions" for more information about TRUE, FALSE, and UNKNOWN conditions.)

Examples

The first two examples are for a library database.

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

The following example is for a sales database.

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
     )