Creates a temporary or persistent base table definition. A table
definition consists of a list of definitions of columns that make
up a row in the table.
Persistent base tables are tables whose metadata and data are
stored in the database beyond an SQL session. The data can be
shared by all users attached to the database.
Temporary tables are tables whose data is automatically deleted
when an SQL session or module ends. The tables only materialize
when you refer to them in an SQL session and the data is local
to an SQL session. You can also specify whether the data is
preserved or deleted at the end of a transaction within the
session; the default is to delete the data. The data in temporary
tables is private to the user. There are three types of temporary
tables:
o Global temporary tables
o Local temporary tables
o Declared local temporary tables (see the DECLARE LOCAL_
TEMPORARY_TABLE for additional information)
The metadata for a global temporary table is stored in the
database and persists beyond the SQL session. Different SQL
sessions can share the same metadata. The data stored in the
table cannot be shared between SQL sessions. However, the data
can be shared between modules in a single SQL session. The data
does not persist beyond an SQL session.
The metadata for a local temporary table is stored in the
database and persists beyond the SQL session. Different SQL
sessions can share the same metadata. The data stored in the
table cannot be shared between different modules in a single SQL
session or between SQL sessions. The data does not persist beyond
an SQL session or module.
Because temporary tables are used only to hold the user's data,
which is not shared among users, no locks are needed and the data
can be modified in a read-only transaction.
See the Oracle Rdb Guide to Database Design and Definition for
more information on temporary tables.
Information tables are special read-only tables that can be
used to retrieve database attributes that are not stored in the
existing relational tables. Information tables allow interesting
database information, which is currently stored in an internal
format, to be displayed as a relational table.
When you define a table, you can also define table constraints. A
constraint specifies a condition that restricts the values that
can be stored in a table. Constraints can specify that columns
contain:
- Only certain values
- Primary key values
- Unique values
- Values that cannot be null
There are several ways to specify a table definition in the
CREATE TABLE statement:
o Directly by naming the table, its columns and associated
data types, default values (optional), constraint definitions
(optional), and formatting clauses.
You can define constraints on persistent base tables and
global temporary tables only.
o Indirectly by providing a path name for a repository record
definition that specifies the table name, columns, and data
types.
o Indirectly by providing another table as a model in inheriting
the columns, datatypes and NOT NULL constraints.
SQL allows you to specify the default character data type or the
national character data type when defining table columns.
1 – Environment
You can use the CREATE TABLE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
CREATE -+---------------------------+-> TABLE --------+
+-> INFORMATION ------------+ |
+-> GLOBAL -+-> TEMPORARY --+ |
+-> LOCAL --+ |
+---------------------- <------------------------------+
++-> FROM <path-name> -+-------------------+-----------------------+
| +-> ALIAS <alias> --+ |
+-> <table-name> -+-------------------------------+-> table-body -+
+-> STORED NAME IS stored-name -+ |
+---------------------<------------------------------------------+
+-+--------------------------------+->
+-+--> create-table-attributes -++
+---------------<-------------+
create-table-attributes =
---+-> COMMENT IS --+-> 'quoted-string' -+------+->
| +-------- / ---------+ |
+-> COMPRESSION IS -+-> ENABLED ---+---------+
| +-> DISABLED --+ |
+-> DISABLE --+-> cre-enable-disable --------+
+-> ENABLE ---+ |
+-> LOGGING ---------------------------------+
+-> NOLOGGING -------------------------------+
+-> ON COMMIT -+-> DELETE ----+-> ROWS ------+
+-> PRESERVE --+
table-body =
-+-> (column-constraint-list) ---------------------------------+->
| |
+-> LIKE <other-table-name> --+------------------------------++
+--> (column-constraint-list) -+
cre-enable-disable =
-+-> ALL CONSTRAINTS ---------------------+->
+-> CONSTRAINT <constraint-name> --------+
+-> PRIMARY KEY -------------------------+
+-> UNIQUE ( -+- <column-name> +-> ) ----+
+------- , <-----+
col-definition =
--> <column-name> --+
+---------------+
+---> column-type ---+--------------------------+-+--+
| +->DEFAULT value-expr | | |
| +->column-identity --------+ | |
+---> COMPUTED BY value-expr ---------------------+ |
+-------------------------<--------------------------+
+-+--------------------+----------------+
+-> col-constraint --+ |
+--------------------------<------------+
++------------------------+-----+-----------------------+->
+-> comment-is-clause --+ +-> sql-and-dtr-clause -+
column-constraint-list =
--+--+-> col-definition -------+-----+->
| +-> table-constraint -----+ |
+---------------- , <--------------+
column-identity =
--> IDENTITY --+--------------------------------------------------+-->
+- ( <start-with> --+----------------------+-> ) --+
+-> , <increment-by> --+
column-type=
--+-> data-type -----------------------------------+-->
+-> <domain-name> -------------------------------+
+-> references-clause ---------------------------+
+-> AUTOMATIC --+-----------+--> AS value-expr --+
+-> INSERT -+
+-> UPDATE -+
data-type =
-+-> char-data-types -----------------------------------------+-->
+-> TINYINT --------------+-----+------------+---------------+
+-> SMALLINT -------------+ +-> ( <n> ) -+ |
+-> INTEGER --------------+ |
+-> BIGINT ---------------+ |
+-> FLOAT ----------------+ |
+-> NUMBER -+----------------------------------+-------------+
| +-> ( -+-> <p> -+-+----------+-> ) + |
| +-> * ---+ +-> , <d> -+ |
+-> LIST OF BYTE VARYING --+------------+--+--------------+--+
| +-> ( <n> ) -+ +-> AS BINARY -+ |
| +-> AS TEXT ---+ |
+-> DECIMAL -++------------------------------+---------------+
+-> NUMERIC -++-> ( --> <n> +----------+-> ) + |
| +-> , <n> -+ |
+-> REAL ----------------------------------------------------+
+-> DOUBLE PRECISION ----------------------------------------+
+-> date-time-data-types ------------------------------------+
char-data-types =
-+-> CHAR -------------++------------++--------------------------------+-+->
+-> CHARACTER --------++-> ( <n> ) -++-> CHARACTER SET char-set-name -+ |
+-> CHAR VARYING -----+ |
+-> CHARACTER VARYING + |
+-> VARCHAR --+> ( <n> ) ---+--------------------------------+----------+
+-> VARCHAR2 -+ +-> CHARACTER SET char-set-name -+ |
+-> LONG VARCHAR ------------------------------------------------------+
+-> NCHAR --------------+-+------------+--------------------------------+
+-> NATIONAL CHAR ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER -+ |
+-> NCHAR VARYING --------------+-+------------+------------------------+
+-> NATIONAL CHAR VARYING ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER VARYING -+ |
+-> RAW -> ( <n> ) -----------------------------------------------------+
+-> LONG -+--------+----------------------------------------------------+
+-> RAW -+
date-time-data-types =
--+-> DATE -+----------+-----------------+-->
| +-> ANSI -+ |
| +-> VMS ---+ |
+-> TIME ---> frac --------------------+
+-> TIMESTAMP --> frac ----------------+
+-> INTERVAL ---> interval-qualifier --+
col-constraint=
---+---------------------------------++
+-> CONSTRAINT <constraint-name> -+|
+------------------------------------+
+-> PRIMARY KEY -----------------+
+-> UNIQUE ----------------------+
+-> NOT NULL --------------------+
+-> NULL ------------------------+
+-> CHECK (predicate) -----------+
+-> references-clause -----------+
+------------>-------------------+
+-------------<------------------+
+----+----------------------------+-->
+--> constraint-attributes --+
references-clause =
REFERENCES <referenced-table-name> -+
+-----------------<----------------+
+-+--------------------->-------------------+->
+-> ( -+> <referenced-column-name> +-> ) -+
+----------- , <------------+
sql-and-dtr-clause =
-+-> QUERY HEADER IS -+> <quoted-string> +-------------------+->
| +------ / <--------+ |
+-> EDIT STRING IS <quoted-string> -------------------------+
| |
+-> QUERY NAME FOR -+-> DTR --------+-> IS <quoted-string> -+
| +-> DATATRIEVE -+ |
+-> DEFAULT VALUE FOR -+-> DTR --------+-> IS literal ----+
+-> DATATRIEVE -+
literal =
--+-> numeric-literal ----+--->
+-> string-literal -----+
+-> date-time-literal --+
+-> interval-literal ---+
+-> dbkey-literal ------+
table-constraint =
---+---------------->------------------+----+
+-> CONSTRAINT <constraint-name> ---+ |
+------------------------------------------+
+--> table-constraint-clause --------------+
+------------------------------------------+
+---+---------------------------+------------->
+-> constraint-attributes --+
table-constraint-clause =
-+----------------------------------------------+-->
+-> PRIMARY KEY -> ( -+-> <column-name> -+> ) -+
| +------- , <-------+ |
+-> UNIQUE -> ( -+> <column-name> +-> ) -------+
| +------- , <-----+ |
+-> CHECK (predicate) -------------------------+
+-> FOREIGN KEY -> ( -+> <column-name> +-> ) + |
+------- , <-----+ | |
+-----------------------<------------------+ |
+-> references-clause ------------->---------+
constraint-attributes =
-+-> DEFERRABLE -------------+------------------------------+-+->
| +-> INITIALLY +-> IMMEDIATE --++ |
| +-> DEFERRED ---+ |
+-> NOT DEFERRABLE ---------+-------------------------+------+
| +-> INITIALLY IMMEDIATE --+ |
+-> INITIALLY IMMEDIATE ----+-------------------+------------+
| +-> DEFERRABLE -----+ |
| +-> NOT DEFERRABLE -+ |
+-> INITIALLY DEFERRED -----+-------------------+------------+
+-> DEFERRABLE -----+
3 – Arguments
3.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.
3.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.3 – char-data-type
A valid SQL character data type. See the Data_Types HELP topic
for more information on character data types.
3.4 – character-set-name
A valid character set name.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.22 – INFORMATION
Specifies that the table definition is an information table.
Information tables are reserved for use by Oracle Corporation.
3.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.
3.24 – NOT_NULL
Restricts values in the column to values that are not null.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.)
3.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.
3.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.
4 – Examples
Example 1: Creating new tables with primary and foreign keys
In this example, the CREATE TABLE statement is used to create
the EMPLOYEES_2, SALARY_HISTORY_2, and WORK_STATUS_2 tables in
the personnel database. It specifies column definitions based on
domain definitions for the entire database.
The FOREIGN KEY constraint specified in the SALARY_HISTORY_2
table must match the PRIMARY KEY constraint specified in the
EMPLOYEES_2 table.
Note also that the CHECK constraint specified is a table
constraint because it is separated by commas from the column to
which it refers. In this case, a column constraint on EMPLOYEE_ID
would have the same effect because it refers only to the single
column EMPLOYEE_ID.
Because the dialect is SQL99, the default for constraint
evaluation time is NOT DEFERRABLE.
SQL> -- *** Set Dialect ***
SQL> --
SQL> SET DIALECT 'SQL99';
SQL> --
SQL> -- *** Create tables ***
SQL> --
SQL> CREATE TABLE WORK_STATUS_2
cont> (
cont> STATUS_CODE STATUS_CODE_DOM
cont> CONSTRAINT WS2_STATUS_CODE_PRIMARY
cont> PRIMARY KEY,
cont> STATUS_NAME STATUS_NAME_DOM,
cont> STATUS_TYPE STATUS_DESC_DOM
cont> );
SQL> --
SQL> CREATE TABLE EMPLOYEES_2
cont> (
cont> EMPLOYEE_ID ID_DOM
cont> CONSTRAINT E2_EMPLOYEE_ID_PRIMARY
cont> PRIMARY KEY,
cont> LAST_NAME LAST_NAME_DOM,
cont> FIRST_NAME FIRST_NAME_DOM,
cont> MIDDLE_INITIAL MIDDLE_INITIAL_DOM,
cont> ADDRESS_DATA_1 ADDRESS_DATA_1_DOM,
cont> ADDRESS_DATA_2 ADDRESS_DATA_2_DOM,
cont> CITY CITY_DOM,
cont> STATE STATE_DOM,
cont> POSTAL_CODE POSTAL_CODE_DOM,
cont> SEX SEX_DOM
cont> CONSTRAINT EMPLOYEE_SEX_VALUES
cont> CHECK (
cont> SEX IN ('M', 'F') OR SEX IS NULL
cont> ),
cont> BIRTHDAY DATE_DOM,
cont> STATUS_CODE STATUS_CODE_DOM
cont> CONSTRAINT E2_STATUS_CODE_FOREIGN
cont> REFERENCES WORK_STATUS_2 (STATUS_CODE),
cont> CONSTRAINT EMP_STATUS_CODE_VALUES_2
cont> CHECK (
cont> STATUS_CODE IN ('0', '1', '2')
cont> OR STATUS_CODE IS NULL
cont> )
cont> );
SQL> --
SQL> CREATE TABLE SALARY_HISTORY_2
cont> (
cont> EMPLOYEE_ID ID_DOM
cont> CONSTRAINT SH2_EMPLOYEES_ID_FOREIGN
cont> REFERENCES EMPLOYEES_2 (EMPLOYEE_ID),
cont> SALARY_AMOUNT SALARY_DOM,
cont> SALARY_START DATE_DOM,
cont> SALARY_END DATE_DOM
cont> );
SQL>
Example 2: Creating a table with many SQL data types
The following example is an excerpt from the sample program sql_
all_datatypes created during installation of Oracle Rdb in the
Samples directory. For a variety of languages, sql_all_datatypes
illustrates how you declare program variables to match a variety
of data types, and how you can specify those variables in SQL
statements when you store and retrieve column values or null
values.
This example shows the CREATE TABLE statement from the sql_all_
datatypes program.
EXEC SQL CREATE TABLE ALL_DATATYPES_TABLE
(
CHAR_COL CHAR(10),
SMALLINT_COL SMALLINT,
SMALLINT_SCALED_COL SMALLINT (3),
INTEGER_COL INTEGER,
INTEGER_SCALED_COL INTEGER (2),
QUADWORD_COL QUADWORD,
QUADWORD_SCALED_COL QUADWORD (5),
REAL_COL REAL,
DOUBLE_PREC_COL DOUBLE PRECISION,
DATE_COL DATE,
VARCHAR_COL VARCHAR(40)
);
Example 3: Specifying default values for columns
The following example illustrates the use of default values
for columns. Each salesperson enters his or her own daily sales
information into the DAILY_SALES table.
SQL> --
SQL> CREATE TABLE DAILY_SALES
cont> --
cont> -- The column SALESPERSON is based on LAST_NAME_DOM and
cont> -- the default value is the user name of the person who
cont> -- enters the information:
cont> (SALESPERSON LAST_NAME_DOM DEFAULT USER,
cont> --
cont> -- Typical work day is 8 hours:
cont> HOURS_WORKED SMALLINT DEFAULT 8,
cont> HOURS_OVERTIME SMALLINT,
cont> GROSS_SALES INTEGER );
SQL> --
SQL> -- Insert daily sales information accepting the
SQL> -- default values for SALESPERSON and HOURS_WORKED:
SQL> --
SQL> INSERT INTO DAILY_SALES
cont> (HOURS_OVERTIME, GROSS_SALES )
cont> VALUES
cont> (1, 2499.00);
1 row inserted
SQL> SELECT * FROM DAILY_SALES;
SALESPERSON HOURS_WORKED HOURS_OVERTIME GROSS_SALES
KILPATRICK 8 1 2499
1 row selected
Example 4: Violating a constraint indirectly with the DELETE
statement
Constraints prevent INSERT statements from adding rows to a table
that do not satisfy conditions specified in the constraint.
Constraints also prevent DELETE or UPDATE statements from
deleting or changing values in a table if the deletion or change
violates the constraint on another table in the database. The
following example illustrates that point:
SQL> -- TEST has no constraints defined for it, but it is subject to
SQL> -- restrictions nonetheless because of the constraint specified
SQL> -- in TEST2:
SQL> CREATE TABLE TEST
cont> (COL1 REAL);
SQL>
SQL> CREATE TABLE TEST2
cont> (COL1 REAL,
cont> CHECK (COL1 IN
cont> (SELECT COL1 FROM TEST))
cont> );
SQL> COMMIT;
SQL>
SQL> INSERT INTO TEST VALUES (1);
1 row inserted
SQL> INSERT INTO TEST2 VALUES (1);
1 row inserted
SQL> COMMIT;
SQL> -- This DELETE statement will fail because it will cause COL1 in
SQL> -- TEST2 to contain a value without the same value in COL1 of TEST:
SQL> DELETE FROM TEST WHERE COL1 = 1;
1 row deleted
SQL> COMMIT;
%RDB-E-INTEG_FAIL, violation of constraint TEST2_CHECK1 caused operation to
fail
Example 5: Evaluating constraints at verb time
Deferrable constraints are not evaluated until a transaction
issues a COMMIT statement. You can specify that constraints be
evaluated more frequently with the EVALUATING clause of the SET
TRANSACTION statement.
SQL> create table TEST
cont> (col1 integer,
cont> col2 integer
cont> constraint C2
cont> unique
cont> deferrable
cont> );
SQL>
SQL> insert into TEST (col1, col2) values (1, 2);
1 row inserted
SQL> commit;
SQL>
SQL> /*
***> This INSERT will violate the constraint as shown by
***> the error during COMMIT
***> */
SQL> insert into TEST (col1, col2) values (1, 2);
1 row inserted
SQL> commit;
%RDB-E-INTEG_FAIL, violation of constraint C2 caused operation to fail
-RDB-F-ON_DB, on database USER_DISK:[DOC.DATABASES]MF_PERSONNEL.RDB;1
SQL> /*
***> The COMMIT failed, so we will ROLLBACK
***> */
SQL> rollback;
SQL>
SQL> /*
***> You can change the evalution time using the EVALUATING
***> clause of SET TRANSACTION
***> */
SQL> set transaction read write evaluating C2 at verb time;
SQL> insert into TEST (col1, col2) values (1, 2);
%RDB-E-INTEG_FAIL, violation of constraint C2 caused operation to fail
-RDB-F-ON_DB, on database USER_DISK:[DOC.DATABASES]MF_PERSONNEL.RDB;1
SQL> rollback;