Creates a new table.
A new table is created as specified.
table-nameis specified in its unqualified form, the table will be created in the schema which has the same name as the current ident.
table-nameis specified in its fully qualified form (i.e.
schema-name.table-name) the table will be created in the named schema (in this case, the current ident must be the creator of the specified schema).
The table definition includes a list of
The table must be created in a databank on which the current ident has
IN databank-nameis not specified, the system will choose a databank on which the user has
TABLEprivilege. If more than one such databank exists, databanks created by the current ident are chosen in preference to others and the databank with the most secure transaction option is chosen (i.e. a databank with
LOGoption would be chosen in preference to one with
TRANSACTIONoption and one with
TRANSACTIONoption in preference to one with
The new table is empty until data is inserted.
The columns will appear in the table in the order specified. Each column name must be unique within the table. Column formats may be specified either by explicit data type, see Data Types in SQL Statements, or by specifying the name of a domain to which the column will belong. In the latter case, all the properties of the domain apply to the column.
A default value can be defined for the column by specifying
column-definitionor by having the column belong to a domain for which a default value is defined. A default value specified in
default-valuewill take precedence over a domain default value and the data type of the value specified in
default-valuemust conform to the data type of the column.
The default value will be assigned to a column whenever an INSERT is performed with no explicit value supplied. If the defined default value does not conform to other constraints, e.g. a
CHECKconstraint, then an
INSERTmust supply a value.
The COLLATE Clause
In order to enable string data comparison and ordering, you can specify a
COLLATEclause for a column.
A collation specified in the column-definition will take precedence over a domain collation.
By doing so, the collation defined will always considered in clauses such as WHERE,
GROUP BY, as well as when using relational and comparison operators. For more information, see the Mimer SQL User's Manual, Collations.
One or more constraints may be defined on the table, either by specifying a
column-definitionor by the specifying a
table-constraint-definitionin the table element list.
All table constraints may be named by specifying a
table-constraint-definition. If a constraint is defined without specifying an explicit name, an automatically generated name will be assigned to it.
Note: Automatically generated constraint names start with SQL_, so it is recommended that this initial character sequence be avoided when explicitly specifying a constraint name.
Constraint names are shown in the appropriate
INFORMATION_SCHEMAviews, see INFORMATION_SCHEMA.
The constraint name is used to identify a constraint when it is dropped using the
ALTER TABLEstatement. For more information, see ALTER TABLE.
NOT NULL Constraints
If this constraint is specified in a
column-definitionfor a column, the column will not accept an attempt to insert the
NULLvalue. (This constraint can also be effectively defined by specifying by a
CHECKconstraint for the table, see below, however this is not recommended because the column would not then be flagged as not accepting
NULLvalues in any
PRIMARY KEY Constraint
PRIMARY KEYcan be defined for the table, composed of one or more of the table columns.
The same column must not occur more than once in the primary key.
A column that is a part of the primary key will implicitly be constrained as
NOT NULL, regardless of any
NOT NULLconstraints explicitly defined on the table. The
NULLvalue cannot, therefore, occur in a primary key column.
The purpose of a primary key is to define a key value that uniquely identifies each table row, therefore the primary key value for each row in the table must be unique.
The primary key value for a table row is the combined value of the column(s) making up the primary key. The column(s) of the primary key (and their order in the key) can be defined using the
PRIMARY KEYclause in a
If the primary key for the table is to be composed of only a single column, then it can be defined by specifying
PRIMARY KEYin a
column-definitionfor that column.
One or more
UNIQUEconstraints can be defined on the table. A
UNIQUEconstraint defines a unique key for the table. A unique key is composed of one or more table columns, just like the primary key. A column must not occur more than once in the same unique key.
A unique key defines a key value that uniquely identifies each row in the table, therefore a table cannot contain two rows which have the same value for a unique key unless one or more of the columns are null.
A unique key must not be composed of the same set of column(s) (occurring in the same order) as either the primary key or an existing unique key defined for the table.
A unique key value for a table row is the combined value of the column(s) making up the unique key. The column(s) of the unique key (and their order in the key) can be defined using the
UNIQUEclause in a
If a unique key is to be composed of only a single column, then it can be defined by specifying
column-definitionfor that column.
Note: Multiple occurrences of the NULL-value do not violate a UNIQUE constraint.
A referential constraint defines a foreign key relationship between the table being created (the referencing table) and another table in the database (the referenced table).
A foreign key relationship exists between a key (the foreign key) in the referencing table and the primary key or one of the unique keys of the referenced table.
The foreign key in the referencing table is defined by using the
FOREIGN KEYclause in
table-constraint-definitionand is composed of one or more columns of the referencing table. The same referencing table column cannot occur more than once in the foreign key.
The corresponding key in the referenced table is specified by using the
references. If a list of column names is not specified after the name of the referenced table, then the primary key of the referenced table is assumed.
More than one foreign key can be defined for a table and the same table column can occur in more than one of the foreign keys.
The name of the referenced table must be specified in its fully qualified form if the name of the schema to which it belongs is not the same as the current ident.
The i-th column in the referencing table foreign key corresponds to the i-th column in the specified key of the referenced table and both keys must be composed of the same number of columns.
The data type and data length of each column in the referencing table foreign key must be identical to the data type and data length of the corresponding column in the specified key of the referenced table.
The effect of a referential constraint is to constrain table data in a way that only allows a row in the referencing table which has a foreign key value that matches the specified key value of a row in the referenced table.
One or more of the columns in a foreign key may permit the
NULLvalue (this will be the case if there is no
NOT NULLconstraint or equivalent
CHECKconstraint in effect for the column).
A referencing table row which has a foreign key value with the
NULLvalue in at least one of the columns will always fulfil the referential constraint and therefore be acceptable as a row in the referencing table.
If all of the columns in a foreign key are constrained not to accept the
NULLvalue, then the only rows that will be accepted in the referencing table are those with a foreign key value that already exists in the corresponding key of the referenced table.
Rules can be defined in
referencesthat specify an action to be performed on the affected row(s) of the referencing table when a
UPDATEoperation in the referenced table causes a referential constraint to be violated (because rows would consequently exist in the referencing table those foreign key value did not match the corresponding key value of a row in the referenced table).
It is possible to explicitly specify
ON UPDATE NO ACTIONin
update-rule(this will also be assumed by default if no
update-ruleis specified). This is to allow for future extensions to the Mimer SQL syntax.
The following actions can be specified in
CASCADE- this will cause the affected rows of the referencing table to be deleted.
SET NULL- the relevant foreign key columns of the affected rows in the referencing table will be set to the
SET DEFAULT- the relevant foreign key columns of the affected rows in the referencing table will be set to the value that would be applied to that column if an INSERT operation is performed without specifying an explicit value (i.e. column default, domain default or the NULL value).
NO ACTION- an error will be raised because the referential constraint has been violated.
delete-ruleis not specified, then the action
NO ACTIONis assumed.
A referential constraint can be defined by specifying a
FOREIGN KEYclause in
If a referencing table foreign key is to be composed of only a single column, then the referential constraint can be defined by specifying
column-definitionfor that column.
One or more check constraints can be defined on the table, which will determine whether the changes resulting from an
UPDATEoperation will be accepted or rejected.
search-conditionwhich defines the check constraint must not contain a select-specification, an invocation of a set function, a reference to a host variable or a non-deterministic expression.
search-conditionof a check constraint specified in a
table-constraint-definitioncontains column references, they must be columns in the table being created.
search-conditionof a check constraint specified in a
column-definitioncontains a column reference, it must be the column identified by
search-conditionof a check constraint defined on the table will be evaluated whenever a new row is inserted into the table and whenever an existing row is updated.
The values for any column reference(s) contained in the
search-conditionwill be taken from the row being inserted or updated.
The data change operation will only be accepted if the search condition does not evaluate to false.
default-value, see Default Values.
search-condition, see Search Conditions.
TABLEprivilege on the databank in which the table is to be created.
The table name must not be the same as the name of any other table, view or synonym belonging to the same schema.
If a domain name is specified for
column-definition, USAGE privilege must be held on the domain.
table-constraint-definitioncan only be specified once in the
UNIQUEconstraint is defined on the table, it must be stored in a databank with the
REFERENTIALconstraint is defined, both the referencing table and the referenced table must be stored in a databank with the
A schema cannot contain two constraints with the same name.
The creator of the table must hold
REFERENCESprivilege on all the columns specified in
The name of a view cannot be specified for
When creating a table with a foreign key, you, the creator, must have exclusive access to the referenced table.
A column of LARGE OBJECT data type is not allowed in any type of table constraint.
The creator of the table is granted all access privileges to the table
WITH GRANT OPTION.
REFERENTIALconstraint, the referenced table can be the same as referencing table. In this situation, the table data is constrained in a way that only allows the foreign key columns to contain key values that are already present in the referenced (primary or unique) key.
The action specified in the
REFERENTIALconstraint does not apply to columns in the referencing table that are already marked for deletion.
If a name is not specified for a table or column constraint, a system generated name is applied to it. System generated names will begin with
SQL_so it is recommended that this starting character sequence be avoided for explicitly specified constraint names.
The constraint characteristics
INITIALLY IMMEDIATE NOT DEFERRABLEcan be explicitly specified to ensure that constraints will be, by default, verified at the time the relevant data manipulation operation is performed rather than when the transaction is committed and that the verification may never be explicitly deferred until the time the transaction is committed. This is also the default behavior. (This is to allow for future extensions to the Mimer SQL syntax.)
The primary key and the unique keys for a table are not dissimilar in their function and they constrain data in the same way apart from the fact that primary key columns are always defined as not null, however a unique key should not be used instead of a primary key. One reason for this is that the primary key is handled more efficiently than the unique keys, so there is a performance advantage. See Relational Databases - Selected Writings by C. J. Date for a discussion of primary and unique keys.
ExampleCREATE TABLE eng_table (col_1 INTEGER, col_2 NCHAR(2000) COLLATE english_1, PRIMARY KEY (col_1));
For many more examples, see the Mimer SQL User's Manual, Creating Tables.
SQL-2003 Core Fully compliant. SQL-2003 Features outside core Feature F691, "Collations and translations" support for collate clause in column definition.Feature F251, "Domain support" support for using domains as data type.Feature F191, "Referential delete action" support for cascade, set null and set default action for on delete.Feature F491, "Constraint management" support for named constraints.Feature T591, "UNIQUE constraints of possibly null columns". Mimer SQL extension Support for the IN databank-name clause is a Mimer SQL extension.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40