CREATE TABLE
Creates
a new table in a database.
SYNTAX
| table_name |
Name
of the new table you want to create. |
| column_name |
Name
of a column you want to create in the table. |
| column_definition |
Definition
for a column. |
| tablespace_name |
Name
of the table the foreign key references. |
| boolean_expression |
Expression
that evaluates to true or false. |
| number |
Value
to use for the fillfactor. |
DESCRIPTION
The CREATE
TABLE command creates a new table. You should specify a tablespace when
you create the table, or DBMaker will create the table in the system tablespace
by default. Any user with RESOURCE or higher security privileges can execute
the CREATE TABLE command.
Tables are
the primary unit of data storage in a relational database, and any information
you enter in a database is stored in tables. Each table represents a single
type of real-world object and contains information on individual objects
of that type. These can be real objects such as customers or products,
or abstract objects such as orders or transactions. Each table in a working
database is given a unique name, and this name normally identifies the
type of object stored in the table. Tables store the information about
the objects they represent in rows and columns.
Rows contain
information that defines a single type of entity that has common characteristics.
Each row represents an individual occurrence of that type of entity. Rows
are identified using one or more of the characteristics of the entity.
Rows do not have any particular order, and there is no guarantee that
rows will be listed in the same order twice. Rows may also be called records
or tuples.
Columns
contain information that defines the characteristics of an entity. Each
column represents one characteristic or item of data that is stored for
each individual occurrence of an entity. Columns are identified using
a descriptive name and a data type. You use the column name to reference
the column, so all columns in a table must have unique names. Since you
refer to columns by name only and never by position, you can rearrange
columns in a table without affecting SQL queries. Columns may also be
called fields or attributes.
The integrity
of data can be ensured by applying constraints, or rules, to ensure the
data meets certain criteria. When you create a table, you can apply domain
and column integrity constraints on individual columns, and table integrity
constraints.
Domain constraints
are defined as part of the domain definition and are applied to all columns
based on the domain. When inserting a new row or updating an existing
row, each domain constraint is evaluated. Domain constraints can include
NULL/ NOT NULL constraints, default values, and CHECK constraints.
Column constraints
are defined on a specific column and do not affect other columns in the
same table. Whenever inserting a new row or updating an existing row,
each column constraint is evaluated. Column constraints can include NULL/
NOT NULL constraints, default values, and CHECK constraints.
Table constraints
are defined on a set of columns. Whenever inserting a new row or updating
an existing row, each table constraint is evaluated after all domain and
column constraints are evaluated as true. Only after the table constraint
is also evaluated as true will the statement be processed. Table constraints
can include UNIQUE and CHECK constraints, primary keys, and foreign keys.
To create
a table, you must provide at least the table name and column definitions.
Tables must have at least one column, and can have as many as 252 columns,
provided the total size of the column does not exceed 3992 bytes.
DBMaker
identifies each table by a unique combination of owner name and table
name, known as the fully qualified name. Table names have a maximum length
of eighteen characters, and may contain numbers, letters, the underscore
character, and the symbols $ and #. The first character may not be a number.
Table names must be unique among all tables created by the user creating
the table. Table names are case-sensitive.
To specify
a column definition, you must provide at least a column name and a data
type or domain. The syntax and usage of keywords used in column definitions
is shown below
Column Definitions
| column_name |
Name
of the column you want to create. |
| data_type |
Name
of the data type you want to use for the column. |
| domain_name |
Name of the domain you want to use in place of a data type. |
| constant |
Constant
value to use if no value is inserted. |
| function_name |
Built-in
function to use if no value is inserted. |
| boolean_expression |
Expressoin
that evaluates to true or false. |
DBMaker
identifies columns in a table by a unique combination of owner name, table
name, and column name, known as the fully qualified name. Column names
have a maximum length of eighteen characters, and may contain numbers,
letters, the underscore character, and the symbols $ and #. The first
character may not be a number. Column names must be unique among all columns
in the same table. Column names are case-sensitive.
You must
specify a data type for each column. DBMaker supports the following data
types: CHAR, INTEGER, SMALLINT, FLOAT, DOUBLE, DECIMAL, DATE, TIME, TIMESTAMP,
VARCHAR, BINARY, LONG VARCHAR, LONG VARBINARY, SERIAL, OID, and FILE.
Optionally,
you can use a domain for a column instead of a data type. Domains are
a combination of data type, default value, and constraint that are applied
to a column when it is defined using a domain as the data type. (See the
column definition DEFAULT and CHECK keywords below for a description of
default values and constraints.) Default values and constraints provided
in the column definition will override those of the domain. Column definitions
can also provide constraints in addition to those of the domain.
As mentioned
previously, the integrity of data you insert into a column can be ensured
by applying constraints to ensure the data meets certain criteria. When
you create a table, you can apply column integrity constraints on each
column in a table. Column integrity constraints can include NULL/NOT NULL
constraints, default values, and CHECK constraints.
The NULL/NOT
NULL keywords are optional. These keywords specify whether a column can
contain a NULL value (i.e. can be left empty) when inserting a new row.
The NULL keyword specifies that a column may contain an undefined value
when a new row is inserted, while the NOT NULL keyword specifies that
a value must be provided when a new row is inserted. If you do not specify
the NULL/NOT NULL keywords, NULL is used by default.
The DEFAULT
keyword is optional. This keyword is used to specify a default value that
will be inserted into a column if no value is provided when inserting
a new row. Constants, results from built-in functions, or the NULL keyword
may be used as the default value. You can only use built-in functions
that have no argument, such as PI(), NOW(), or USER(), when defining a
column. If you use the NULL keyword as the DEFAULT value, the column cannot
be defined with the NOT NULL keyword.
The CHECK
keyword (in the column definition) is optional. This keyword is used to
specify a range of acceptable values (constraints) that may be entered
in a column. The expression that specifies the range of acceptable values
may be any expression that evaluates to true or false. The VALUE keyword
may be used in the expression in conjunction with the CHECK keyword to
represent the value of the column. If an SQL statement does not satisfy
the CHECK conditions, it will not be processed.
Table Options
DBMaker
provides a number of optional features that you can use when creating
a table. You can specify the behavior of these options using the TEMPORARY/
TEMP, IN, CHECK, LOCK MODE, NOCACHE, and FILLFACTOR keywords.
The TEMPORARY/TEMP
keywords are optional. These keywords specify that a table should be created
as a temporary table instead of a permanent table. Data access is faster
in temporary tables since no locks are used and no journal records are
written for temporary tables. However, temporary tables can only be used
by the table owner, and are automatically deleted when you disconnect
from the database. You can also drop a temporary table at any time while
still connected to the database using the DROP TABLE command.
The IN keyword
is optional. This keyword specifies the name of the tablespace the table
will be created in. Tablespaces are the logical areas of storage used
to partition information in a database into manageable areas. This allows
you to separate tables according to logical groupings, or to place frequently
used tables in different storage locations than those you use only infrequently.
If you do not specify a tablespace using this keyword, the table will
be created in the system tablespace by default.
The CHECK
keyword (in the table definition) is optional. This keyword behaves in
a manner similar to the CHECK keyword used in the column definition, but
is normally used to ensure data from multiple columns falls in some range
of acceptable values, instead of specifying a range of acceptable values
that may be entered in a column. The expression that specifies the range
of acceptable values may be any expression that evaluates to true or false.
Column names may be used in the expression in conjunction with the CHECK
keyword to represent the value of a column, in the way the VALUE keyword
is used in a domain or column constraint. If an SQL statement does not
satisfy the CHECK conditions, it will not be processed.
The LOCK
MODE keyword is optional. This keyword specifies the lock mode (lock level)
DBMaker uses when accessing data in a table. DBMaker has three lock modes:
table, page, and row. Page lock mode is used by default if no lock mode
is explicitly specified when creating a table. To determine the lock mode
of a table, you can examine the LOCKMODE column of the SYSTABLE system
table.
LOCK MODE
TABLE locks an entire table. This mode decreases concurrency by preventing
other users from accessing the locked table at the same time, but it also
uses fewer lock resources and requires less memory in the System Control
Area (SCA).
LOCK MODE
PAGE locks a single data page. This mode is a trade-off between concurrency
and lock resources. It provides moderate concurrency since other users
may access data in other pages, but cannot access any data in the same
page.
LOCK MODE
ROW locks a single row. This mode increases concurrency by allowing other
users to access any data except the locked row at the same time, but it
also uses more lock resources and required more memory in the SCA.
FILLFACTOR
specifies the percentage of a data page that can be filled before new
records can no longer be inserted. This allows the database to optimize
the use of data pages by reserving space on a data page for updates to
existing records. The number parameter can have a value from 50 to 100,
which represents a fillfactor of 50% to 100%. To determine the fillfactor
of a table, you can examine the FILLFACTOR column of the SYSTABLE system
table.
NOCACHE
limits the number of page buffers used to cache data during a table scan.
DBMaker stores page buffers in a buffer chain with the most recently used
page at one end and the least recently used page at the other end. When
the NOCACHE option is turned on, data pages read during a table scan are
placed at the least recently used end of the buffer chain. Since the least
recently used end of the buffer chain will be flushed before the most
recently used end, subsequent data pages read during the table scan will
replace the previous page. This effectively limits the page buffers used
during a table scan to one page buffer. To determine the cache mode of
a table, you can examine the CACHEMODE column of the SYSTABLE system table.
UPDATE STATISTICS
EVERY N DAYS sets the time interval in days DBMaker will use to automatically
update statistics values. Keeping statistics information current allows
the DBMaker query optimizer to perform queries more efficiently. However,
database administrators may often forget to manually update statistics
values on a regular basis, leading to outdated statistics information
and inefficient queries. You can set the interval DBMaker will use to
update statistics from 1 day to 32768 days.
When you
create a table, you are the table owner. You have all object privileges
on the table, and may assign object privileges for that table to other
users. As the table owner, you will retain all object privileges on the
table even if your security privilege is reduced to CONNECT.
EXAMPLES
The following
example creates a table named Scores in the system tablespace with StudentNo,
Math, English, Science, and History columns. All columns are defined with
the INTEGER data type.
CREATE TABLE Scores (StudentNo INTEGER,
Math INTEGER,
English INTEGER,
Science INTEGER,
History INTEGER)
|
The following
example creates the same table from the example above, but in the StudentRecords
tablespace. No columns may contain NULL values, and a default value of
zero is assigned to the Math, English, Science, and History columns.
CREATE TABLE Scores (StudentNo INTEGER NOT NULL,
Math INTEGER NOT NULL DEFAULT = 0,
English INTEGER NOT NULL DEFAULT = 0,
Science INTEGER NOT NULL DEFAULT = 0,
History INTEGER NOT NULL DEFAULT = 0)
IN StudentRecords
|
The following
example creates the same table from the example above, but the Math, English,
Science and History columns must contain values between 0 and 100.
CREATE TABLE Scores (StudentNo INTEGER NOT NULL,
Math INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
English INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
Science INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
History INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100)
IN StudentRecords
|
The following
example creates the same table from the example above, but defines a table
constraint to ensure the sum of the Math, English, Science and History
columns is less than 400, sets the lock mode to PAGE, specifies a FILLFACTOR
of 90, and turns on the NOCACHE option.
CREATE TABLE Scores (StudentNo INTEGER NOT NULL,
Math INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
English INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
Science INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
History INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100)
IN StudentRecords
CHECK MAth + English + Science + History <= 400
|
The following
example creates the same table from the example above, but sets the lock
mode to PAGE, specifies a FILLFACTOR of 90, and turns on the NOCACHE option.
CREATE TABLE Scores (StudentNo INTEGER NOT NULL,
Math INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
English INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
Science INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100,
History INTEGER NOT NULL DEFAULT = 0
CHECK VALUE <= 0 AND VALUE >= 100)
IN StudentRecords
CHECK MAth + English + Science + History <= 400
LOCK MODE PAGE
FILLFACTOR 90
NOCACHE
|
RELATED COMMANDS
<
CREATE SYNONYM | Contents
| CREATE TABLESPACE >
|
