|
|
|
CREATE TABLE
Description
Creates a table definition. A table definition consists of a list of column definitions that make up a table row. FairCom DB SQL provides two forms of the CREATE TABLE statement. The first form explicitly specifies column definitions. The second form, with the AS query_expression clause, implicitly defines the columns using the columns in the query expression.
Syntax
CREATE TABLE [ IF NOT EXISTS ] [ owner_name. ] table_name
( column_definition [ , { column_definition | table_constraint } ] ... )
[ STORAGE_ATTRIBUTES 'attributes' ]
;
CREATE TABLE [ IF NOT EXISTS ] [ owner_name. ] table_name
[ ( column_name [NULL | NOT NULL], ... ) ]
[ STORAGE_ATTRIBUTES 'attributes' ]
AS query_expression
;
column_definition ::
column_name data_type
[ DEFAULT { literal | USER | NULL | UID
| SYSDATE | SYSTIME | SYSTIMESTAMP } ]
| [ IDENTITY [(seed, increment)] | auto_increment [(seed, increment)] ]
[ column_constraint [ column_constraint ... ] ]
Arguments
IF NOT EXISTS
This argument avoids failure by creating the table only if a table of the same name does not already exist.
owner_name
Specifies the owner of the table. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.
table_name
Names the table definition. FairCom DB SQL defines the table in the database named in the last CONNECT statement.
column_name data_type
Names a column and associates a data type with it. The column names specified must be different than other column names in the table definition. The data_type must be one of the supported data types described in Data Types.
DEFAULT
Specifies an explicit default value for a column. The column takes on the value if an INSERT statement does not include a value for the column. If a column definition omits the DEFAULT clause, the default value is NULL.
The DEFAULT clause accepts the following arguments:
literal |
An integer, numeric or string constant. |
USER |
The name of the user issuing the INSERT or UPDATE statement on the table. Valid only for columns defined with character data types. |
NULL |
A null value. |
UID |
The user id of the user executing the INSERT or UPDATE statement on the table. |
SYSDATE |
The current date. Valid only for columns defined with DATE data types. |
SYSTIME |
The current time. Valid only for columns defined with TIME data types. |
SYSTIMESTAMP |
The current date and time. Valid only for columns defined with TIMESTAMP data types. |
IDENTITY
Specifies that the column is an IDENTITY column. An IDENTITY column is one for which the data is not provided by the user but it is automatically generated by the DBMS. There can be only one identity column for a table. A default value cannot be specified for an identity column. IDENTITY can only be specified on columns of type INTEGER, SMALLINT, TINTYINT and BIGINT.
seed
Specifies a value that used for the identity column when the first row is inserted into the table. Specifying a seed value is optional. If a seed value is not specified, then it defaults to 1.
increment
Specifies a value by which the identity value of the previous row is incremented, which is then used for the identity column when the second and subsequent rows are inserted into the table. Specifying the increment value is optional. If it is not specified, then it defaults to 1.
Note: Although both the seed and increment values are optional, they must both be specified or both be left unspecified. It is not possible to specify only one of them.
auto_increment
auto_increment is specified and behaves exactly like IDENTITY, however, values can be automatically overridden from the INSERT statement. When an INSERT statement specifies a value for an IDENTITY column, it inserts the specified value; when the value is omitted, it is generated automatically. This provides flexibility many ORM provides such as SQL Alchemy depend on.
column_constraint
Specifies a constraint that applies while inserting or updating a value in the associated column. For more information, see Column Constraints.
table_constraint
Specifies a constraint that applies while inserting or updating a row in the table. For more information, see Table Constraints.
STORAGE_ATTRIBUTES 'attributes'
Specifies a string describing underlying FairCom DB table storage attributes. For more information see Storage Attributes.
AS query_expression
Specifies a query expression to use for the data types and contents of the columns for the table. The types and lengths of the columns of the query expression result become the types and lengths of the respective columns in the table created. The rows in the resultant set of the query expression are inserted into the table after creating the table. In this form of the CREATE TABLE statement, column names are optional.
If omitted, the names for the table columns are also derived from the query expression. For more information, see Query Expressions.
Examples
In the following example, the user issuing the CREATE TABLE statement must have REFERENCES privilege on the column “itemno” of the table “john.item”.
CREATE TABLE supplier_item (
supp_no INTEGER NOT NULL PRIMARY KEY,
item_no INTEGER NOT NULL REFERENCES john.item (itemno),
qty INTEGER
) ;
The following CREATE TABLE statement explicitly specifies a table owner, admin:
CREATE TABLE admin.account (
account integer,
balance money (12),
info char (84)
) ;
The following example shows the AS query_expression form of CREATE TABLE to create and load a table with a subset of the data in the customer table:
CREATE TABLE admin.dealer (name, street, city, state)
AS
SELECT name, street, city, state
FROM customer
WHERE customer.state IN ('CA','NY', 'TX') ;
The following example includes a NOT NULL column constraint and DEFAULT clauses for column definitions:
CREATE TABLE emp (
empno integer NOT NULL,
deptno integer DEFAULT 10,
join_date date DEFAULT NULL
) ;
Authorization
The user executing this statement must have either DBA or RESOURCE privilege. If the CREATE TABLE statement specifies a foreign key that references a table owned by a different user, the user must have the REFERENCES privilege on the corresponding columns of the referenced table.
The AS query_expression form of CREATE TABLE requires the user to have select privilege on all the tables and views named in the query expression.
SQL Compliance |
SQL-92, ODBC Minimum SQL grammar. Extensions: AS query_expression |
Environment |
Embedded SQL, interactive SQL, ODBC applications |
Related Statements |
DROP TABLE, ALTER TABLE, Query Expressions |
See Also:
