Collection Contents Index CREATE SERVER statement CREATE TRIGGER statement pdf/chap9.pdf

Reference Manual
   CHAPTER 9. SQL Statements     

CREATE TABLE statement


Function 

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 [ column-constraint ... ] | table-constraint }, ... )
... [ { IN | ON } dbspace-name ]
... [ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ AT location-string ]

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

Permissions 

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.

Side effects 

Automatic commit.

See also 

DROP statement

ALTER TABLE statement

CREATE DBSPACE statement

CREATE EXISTING TABLE statement

SQL Data Types

Creating tables

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 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 Info     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:

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

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

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

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

Remote tables 

Standards and compatibility 

Examples 


Collection Contents Index CREATE SERVER statement CREATE TRIGGER statement pdf/chap9.pdf