Table of Contents Previous Next


3 The SQL Language : 3.3 SQL Commands : 3.3.29 CREATE TABLE

CREATE TABLE -- define a new table
{ column_name data_type [ DEFAULT default_expr ]
[ column_constraint [ ... ] ] | table_constraint } [, ...]
[ TABLESPACE tablespace ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
CHECK (expression) |
REFERENCES reftable [ ( refcolumn ) ]
[ ON DELETE action ] }
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ...] )
PRIMARY KEY ( column_name [, ...] )
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ...] )
REFERENCES reftable [ ( refcolumn [, ...] ) ]
[ ON DELETE action ] }
CREATE TABLE will create a new, initially empty table in the current database. The table will be owned by the user issuing the command.
If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema. Temporary tables exist in a special schema, so a schema name may not be given when creating a temporary table. The table name must be distinct from the name of any other table, sequence, index, or view in the same schema.
CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.
If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. In addition, temporary tables are not visible outside the session in which it was created. (This aspect of global temporary tables is not compatible with Oracle databases.) Any indexes created on a temporary table are automatically temporary as well.
DEFAULT default_expr
The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). The data type of the default expression must match the data type of the column.
CONSTRAINT constraint_name
This clause is only available for compatibility with non-standard SQL databases. Its use is discouraged in new applications.
UNIQUE - column constraint
UNIQUE (column_name [, ...] ) - table constraint
The UNIQUE constraint specifies that a group of one or more distinct columns of a table may contain only unique values. The behavior of the unique table constraint is the same as that for column constraints, with the additional capability to span multiple columns.
PRIMARY KEY - column constraint
PRIMARY KEY ( column_name [, ...] ) - table constraint
The primary key constraint specifies that a column or columns of a table may contain only unique (non-duplicate), non-null values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables may rely on this set of columns as a unique identifier for rows.
CHECK (expression)
The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or “unknown” succeed. Should any row of an insert or update operation produce a FALSE result an error exception is raised and the insert or update does not alter the database. A check constraint specified as a column constraint should reference that column’s value only, while an expression appearing in a table constraint may reference multiple columns.
Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.
REFERENCES reftable [ ( refcolumn ) ] [ ON DELETE action ] - column constraint
FOREIGN KEY ( column [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ] [ ON DELETE action ] - table constraint
These clauses specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table. If refcolumn is omitted, the primary key of the reftable is used. The referenced columns must be the columns of a unique or primary key constraint in the referenced table.
In addition, when the data in the referenced columns is changed, certain actions are performed on the data in this table’s columns. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. Referential actions cannot be deferred even if the constraint is deferrable. Here are the following possible actions for each clause:
DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED
If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.
TABLESPACE tablespace
The tablespace is the name of the tablespace in which the new table is to be created. If not specified, default tablespace is used, or the database’s default tablespace if default_tablespace is an empty string.
This clause allows selection of the tablespace in which the index associated with a UNIQUE or PRIMARY KEY constraint will be created. If not specified, default tablespace is used, or the database’s default tablespace if default_tablespace is an empty string.
Advanced Server automatically creates an index for each unique constraint and primary key constraint to enforce the uniqueness. Thus, it is not necessary to create an explicit index for primary key columns. (See CREATE INDEX for more information.)
Create table dept and table emp:
Define a unique table constraint for the table dept. Unique table constraints can be defined on one or more columns of the table.
Define a primary key table constraint for the table jobhist. Primary key table constraints can be defined on one or more columns of the table.
This assigns a literal constant default value for the column, job and makes the default value of hiredate be the date at which the row is inserted.
Create table dept in tablespace diskvol1:

3 The SQL Language : 3.3 SQL Commands : 3.3.29 CREATE TABLE

Table of Contents Previous Next