1 – ALIAS alias
Specifies a name for an attach to a particular database. SQL adds
the table definition to the database referred to by the alias.
If you do not specify an alias, SQL adds the table definition to
the default database. See the User_Supplied_Names HELP topic for
more information on default databases and aliases.
2 – AUTOMATIC
Syntax options:
AUTOMATIC AS value-expr
AUTOMATIC INSERT AS value-expr
AUTOMATIC UPDATE AS value-expr
These AUTOMATIC column clauses allow you to store special
information when data is inserted into a row or a row is updated.
For example, you can log application-specific information to
audit activity or provide essential values, such as time stamps
or unique identifiers for the data.
The assignment of values to these types of columns is managed by
Oracle Rdb. The AUTOMATIC INSERT clause can be used to provide
a complex default for the column when the row is inserted; it
cannot be changed by an UPDATE statement. The AUTOMATIC UPDATE
clause can be used to provide an updated value during an UPDATE
statement. The unqualified AUTOMATIC clause specifies that the
value expression should be applied during both INSERT and UPDATE
statements. The column type is derived from the AS value-expr;
using CAST allows a specific data type to be specified. However,
this is not required and is rarely necessary.
You can define an AUTOMATIC INSERT column to automatically
receive data during an insert operation. The data is stored like
any other column, but the column is read-only. Because AUTOMATIC
columns are treated as read-only columns, they cannot appear in
the column list for an insert operation nor be modified by an
update operation. AUTOMATIC UPDATE columns can have an associated
default value that will be used when the row is inserted.
3 – char-data-type
A valid SQL character data type. See the Data_Types HELP topic
for more information on character data types.
4 – character-set-name
A valid character set name.
5 – CHECK predicate
Specifies a predicate that column values inserted into the
table must satisfy. See the Predicates HELP topic for details
on specifying predicates.
Predicates in CHECK column constraints can refer directly only to
the column with which they are associated.
6 – col-constraint
A constraint that applies to values stored in the associated
column.
SQL allows column constraints and table constraints. The five
types of column constraints are PRIMARY KEY, UNIQUE, NOT NULL,
CHECK, and FOREIGN KEY constraints. The FOREIGN KEY constraints
are created with the REFERENCES clause.
You can define a column constraint on persistent base tables and
global temporary tables only.
7 – col-definition
The definition for a column in the table. SQL gives you two ways
to specify column definitions:
o By directly specifying a data type to associate with a column
name
o By naming a domain that indirectly specifies a data type to
associate with a column name
Either way also allows options for specifying default values,
column constraints, and formatting clauses.
8 – column-name
The name of a column you want to create in the table. You need to
specify a column name whether you directly specify a data type in
the column definition or indirectly specify a data type by naming
a domain in the column definition.
9 – COMPUTED_BY
Specifies that the value of this column is calculated from values
in other columns and constant expressions.
If your column definition refers to a column name within a value
expression, that named column must already be defined within the
same CREATE TABLE statement. See the Value_Expressions HELP topic
for information on value expressions.
Any column that you refer to in the definition of a computed
column cannot be deleted from that table unless you first delete
the computed column.
SQL does not allow the following for computed columns:
o UNIQUE constraints
o REFERENCES clauses
o PRIMARY KEY constraints
o DEFAULT clause
o IDENTITY clause
o Default value for DATATRIEVE
For example, if the FICA_RATE for an employee is 6.10 percent of
the employee's starting salary and the group insurance rate is
0.7 percent, you can define FICA_RATE and GROUP_RATE columns like
this:
SQL> CREATE TABLE payroll_detail
cont> (salary_code CHAR(1),
cont> starting_salary SMALLINT(2),
cont> fica_amt
cont> COMPUTED BY (starting_salary * 0.061),
cont> group_rate
cont> COMPUTED BY (starting_salary * 0.007));
When you use this type of definition, you only have to store
values in the salary_code and starting_salary columns. The FICA
and group insurance deduction columns are computed automatically
when the columns fica_amt or group_rate are selected.
10 – COMPRESSION_IS
Syntax options:
COMPRESSION IS ENABLED | COMPRESSION IS DISABLED
Specifies whether run-length compression is enabled or disabled
for rows inserted into the base or temporary table.
In some cases, the data inserted into a table may not compress
and so incur only overhead in the row. This overhead is used
by Rdb to describe the sequence of uncompressible data. Use
COMPRESSION IS DISABLED to prevent Rdb from attempting the
compression of such data.
Any storage map which specifies the ENABLE COMPRESSION or DISABLE
COMPRESSION clause will override this setting in the table.
The COMPRESSION IS clause is not permitted for INFORMATION
tables.
The default is COMPRESSION IS ENABLED.
11 – constraint-attributes
Although the constraint attribute syntax provides 11 permutations
as required by the SQL99 standard, they equate to the following
three options:
o INITIALLY IMMEDIATE NOT DEFERRABLE
Specifies that evaluation of the constraint must take place
when the INSERT, DELETE, or UPDATE statement executes. If
you are using the SQL99, SQL92, MIA, ORACLE LEVEL1, or ORACLE
LEVEL2 dialect, this is the default.
o INITIALLY IMMEDIATE DEFERRABLE
Specifies that evaluation of the constraint may be deferred
(using the SET CONSTRAINT ALL statement or the SET TRANSACTION
statement with the EVALUATING clause), but by default it
is evaluated after the INSERT, DELETE, or UPDATE statement
executes. See the SET_ALL_CONSTRAINTS statement for more
information.
o INITIALLY DEFERRED DEFERRABLE
Specifies that evaluation of the constraint can take place
at any later time. Unless otherwise specified, evaluation of
the constraint takes place as the COMMIT statement executes.
You can use the SET ALL CONSTRAINTS statement to have all
constraints evaluated earlier. See the description of the SET
ALL CONSTRAINTS statement for more information.
If you are using the default SQLV40 dialect, this is the
default constraint attribute. When using this dialect, Oracle
Rdb displays a deprecated feature message for all constraints
defined without specification of one of the constraint
attributes.
12 – CONSTRAINT
Specifies a name for a column or table constraint. The name is
used for a variety of purposes:
o The RDB$INTEG_FAIL error message specifies the name when an
INSERT, UPDATE, or DELETE statement violates the constraint.
o The ALTER TABLE table-name DROP CONSTRAINT constraint-name
statement specifies the name to delete a table constraint.
o The SHOW TABLE statements display the names of column and
table constraints.
o The EVALUATING clause of the SET TRANSACTION and DECLARE
TRANSACTION statements specifies constraint names.
o The ENABLE and DISABLE clauses of the ALTER and CREATE TABLE
statements specify constraint names.
o The ALTER CONSTRAINT statement specifies constraint names.
o The DROP CONSTRAINT statement
The CONSTRAINT clause is optional. If you omit the constraint
name, SQL creates a name. However, Oracle Rdb recommends that
you always name column and table constraints. If you supply a
constraint name with the CONSTRAINT clause, it must be unique
in the database or in the schema if you are using a multischema
database.
13 – data-type
A valid SQL data type. Specifying an explicit data type to
associate with a column is an alternative to specifying a domain
name. See the Data_Types HELP topic for more information on data
types.
14 – date-time-data-types
A data type that specifies a date, time, or interval. See the
Data_Types HELP topic for more information about date-time data
types.
15 – DEFAULT value-expr
Provides a default value for a column if the row that is inserted
does not include a value for that column.
You can use any value expression including subqueries,
conditional, character, date/time, and numeric expressions as
default values. See Value Expressions for more information about
value expressions.
For more information about NULL, see the NULL_Keyword HELP topic.
The value expressions described in Value Expressions include
DBKEY and aggregate functions. However, the DEFAULT clause is
not a valid location for referencing a DBKEY or an aggregate
function. If you attempt to reference either, you receive a
compile-time error.
If you do not specify a default value, a column inherits the
default value from the domain. If you do not specify a default
value for either the column or domain, SQL assigns NULL as the
default value.
16 – domain-name
The name of a domain created in a CREATE DOMAIN statement. SQL
gives the column the data type specified in the domain. For more
information on domains, see the CREATE DOMAIN statement.
For most purposes, you should specify a domain instead of an
explicit data type.
o Domains ensure that all columns in multiple tables that serve
the same purpose have the same data type. For example, several
tables in the sample personnel database refer to the domain
ID_DOM.
o A domain lets you change the data type for all columns that
refer to it in one operation by changing the domain itself
with an ALTER DOMAIN statement.
For example, if you want to change the data type for the
column EMPLOYEE_ID from CHAR(5) to CHAR(6), you need only
alter the data type for the domain ID_DOM. You do not have to
alter the data type for the column EMPLOYEE_ID in the tables
DEGREES, EMPLOYEES, JOB_HISTORY, or SALARY_HISTORY, nor do you
have to alter the column MANAGER_ID in the DEPARTMENTS table.
However, you might not want to use domains when you create tables
if:
o Your application must be compatible with Oracle RDBMS.
o You are creating intermediate result tables that do not need
the advantages of domains.
17 – enable-disable-clause
Allows you to enable or disable all constraints, specified
constraints, a primary key, or a unique column name, as described
in the following list. By default, table and column constraints
added during a create table operation are enabled.
o DISABLE ALL CONSTRAINTS
All table and column constraints for this table are disabled.
No error is raised if no constraints are defined on the table.
o ENABLE ALL CONSTRAINTS
All and column constraints for this table are enabled. No
error is raised if no constraints are defined on the table.
o DISABLE CONSTRAINT constraint-name
The named constraint is disabled. The named constraint must be
a table or column constraint for the table.
o ENABLE CONSTRAINT constraint-name
The named constraint is enabled. The named constraint must be
a table or column constraint for the table.
o DISABLE PRIMARY KEY
The primary key for the table is disabled.
o ENABLE PRIMARY KEY
The primary key for the table is enabled.
o DISABLE UNIQUE (column-name)
The matching UNIQUE constraint is disabled. The columns listed
must be columns in the table.
o ENABLE UNIQUE (column-name)
The matching UNIQUE constraint is enabled. The columns listed
must be columns in the table.
18 – FOREIGN_KEY
The name of a column or columns that you want to declare as a
foreign key in the table you are defining (referencing table).
You cannot declare a computed column as a foreign key.
19 – FROM pathname
Specifies the repository path name of a repository record
definition. SQL creates the table using the definition from this
record and gives the table the name of the record definition.
You can create a table using the FROM path-name clause only if
the record definition in the repository was originally created
using the repository Common Dictionary Operator (CDO) utility.
For instance, you cannot create a table using the FROM path-name
clause if the record definition was created in the repository as
part of an SQL session.
If the repository record contains a nested record definition, you
cannot create a table based on it.
Creating a table based on a repository record definition is
useful when many applications share the same definition. Changes
to the common definition can be automatically reflected in all
applications that use it.
NOTE
Changes by other users or applications to the record
definition in the repository affect the table definition
once the database is integrated to match the repository
with an INTEGRATE DATABASE . . . ALTER FILES statement. If
those changes include deleting records or fields on which
tables or table columns are based, any data in the dependent
table or table column is lost after the next INTEGRATE
DATABASE . . . ALTER FILES statement executes.
You can use the FROM clause only if the database was attached
specifying PATHNAME. You can specify either a full repository
path name or a relative repository path name.
You cannot define constraints or any other table definition
clauses, such as DATATRIEVE formatting clauses, when you use
the FROM path-name form of the CREATE TABLE statement. This
restriction does not prevent you from using an ALTER TABLE
statement to add them later.
You cannot use the FROM path-name clause when embedding a CREATE
TABLE statement within a CREATE DATABASE statement.
20 – IDENTITY
Specifies that the column is to be a special read-only identity
column. INSERT will evaluate this column and store a unique value
for each row inserted. Only one column of a table may have the
IDENTITY attribute. Rdb creates a sequence with the same name as
the current table.
See ALTER SEQUENCE and CREATE SEQUENCE for more information.
21 – increment-by
An integer literal value that specifies the increment for the
sequence created for the IDENTITY column. A negative value
creates a descending sequence, and a positive value creates an
ascending sequence. A value of zero is not permitted. If omitted
the default is 1, that is an ascending sequence.
22 – INFORMATION
Specifies that the table definition is an information table.
Information tables are reserved for use by Oracle Corporation.
23 – LIKE other-table-name
Allows a database administrator to copy the metadata for
an existing table and create a new table with similar
characteristics. An optional column list can be used to add extra
columns and contraints to this table. The referenced table must
exist in the same database as the table being created.
Syntax options:
LOGGING | NOLOGGING
The LOGGING clause specifies that the CREATE TABLE statement
should be logged in the recovery-unit journal file (.ruj) and
after-image journal file (.aij).
The NOLOGGING clause specifies that the CREATE TABLE statement
should not be logged in the recovery-unit journal file (.ruj) and
after-image journal file (.aij).
The LOGGING clause is the default.
24 – NOT_NULL
Restricts values in the column to values that are not null.
25 – ON_COMMIT
Syntax options:
ON COMMIT PRESERVE ROWS | ON COMMIT DELETE ROWS
Specifies whether data is preserved or deleted after a COMMIT
statement for global or local temporary tables only.
The default, if not specified, is ON COMMIT DELETE ROWS.
26 – PRIMARY_KEY
A primary key constraint defines one or more columns whose values
make a row in a table different from all others. SQL requires
that values in a primary key column be unique and not null;
therefore, you need not specify the UNIQUE and NOT NULL column
constraints for primary key columns.
You cannot specify the primary key constraint for a computed
column.
When used as a table constraint this clause must be followed by
a list of column names. When used as a column constraint this
clause applies to the named column of the table.
27 – references-clause
Specifies the name of the column or columns that are a unique
key or primary key or in the referenced table. When the
REFERENCES clause is used as a table constraint, the column names
specified in the FOREIGN KEY clause become a foreign key for the
referencing table.
When used as the column type clause, specifies that the type
of the column be inherited from the PRIMARY KEY or UNIQUE index
referenced. Both the data type and domain are inherited.
28 – REFERENCES referenced table name
Specifies the name of the table that contains the unique key
or primary key referenced by the referencing table. To declare
a constraint that refers to a unique or primary key in another
table, you must have the SQL REFERENCES or CREATE privileges to
the referenced table.
29 – referenced-column-name
For a column constraint, the name of the column that is a
unique key or primary key in the referenced table. You cannot
use a computed column as a referenced column name. For a table
constraint, the referenced column name is the name of the column
or columns that are a unique key or primary key in the referenced
table. If you omit the referenced-column-name clause, the primary
key is selected by default. The number of columns and their data
types must match.
30 – sql-and-dtr-clause
Optional SQL formatting clause.
If you specify a formatting clause for a column that is based on
a domain that also specifies a formatting clause, the formatting
clause in the table definition overrides the one in the domain
definition.
31 – start-with
An integer literal value that specifies the starting value for
the sequence created for the IDENTITY column. If omitted the
default is 1.
32 – STORED_NAME_IS
Specifies a name that Oracle Rdb uses to access a table created
in a multischema database. The stored name allows you to access
multischema definitions using interfaces, such as Oracle RMU,
the Oracle Rdb management utility, that do not recognize multiple
schemas in one database. You cannot specify a stored name for
a table in a database that does not allow multiple schemas. For
more details about stored names, see the User_Supplied_Names HELP
topic.
33 – table-constraint
A constraint definition that applies to the whole table.
SQL allows column constraints and table constraints. The four
types of table constraints are PRIMARY KEY, UNIQUE, CHECK, and
FOREIGN KEY constraints.
A column must be defined in a table before you can specify the
column in a table constraint definition.
You can define a table constraint on persistent base tables and
global temporary tables only.
34 – table-name
The name of the table definition you want to create. Use a name
that is unique among all table, sequence, view and synonym names
in the database, or in the schema if you are using a multischema
database. Use any valid SQL name. (See the User_Supplied_Names
HELP topic for more information on user-supplied names.)
35 – temporary_tables
Syntax options:
CREATE GLOBAL TEMPORARY TABLE | CREATE LOCAL TEMPORARY TABLE
Specifies that the table definition is either a global or local
temporary table.
36 – UNIQUE
Specifies that values in the associated column must be unique.
You can use either the UNIQUE or PRIMARY KEY keywords to define
one or more columns as a unique key for a table.
You cannot specify the UNIQUE constraint for a computed column or
for a column defined with the LIST OF BYTE VARYING data type.