CREATE TABLE Statement

Database Tables

Database Table Indexes

Table Column Description

column_description ::= column_name type [ DEFAULT default_value ] 
	[column_property {, column_property}... ] [ language_and_charset ]
default_value ::= expression | USER | UNIQUE 
column_property ::= PRIMARY KEY | UNIQUE | INDEX | NOT NULL | REFERENCES table_name column_name
	| CHECK ( condition )

The column description defines column properties in a table. Column name and column type have to be specified.

The language_and_charset entry can be specified only for character strings.

Default column value

The default column value is assigned to newly created records, if no other value is specified for the column. If the default value is set by a expression, then the expression must be the same type as the columns. Default value cannot be specified for variable-length column.

If an error occurs when assigning default value of a column, only a warning (ERROR_IN_DEFVAL) occurs and the transaction is not rolled back.

Example : functions CURRENT_DATE, CURRENT_TIME or CURRENT_TIMESTAMP are used as default values for columns of DATE, TIME or TIMESTAMP type.

You can use the special USER value as a default value for strings, which designates the user entering the record. USER value is the binary ID of the user for BINARY type columns (binary strings) or the login name of the user for CHAR(n) columns (character strings).

Default value can also be used for generating unique keys. Sequences offer full control over integer keys. Besides that you can use a special default value UNIQUE for INTEGER type columns. This special value assigns column values from a special counter bound to a table.

Unique keys generation using the default values works only if the column values are not additionally modified. Explicit value assigning into such column may block inserting further records, if the key duplicity may occur.

Column properties

Column properties have the following meaning:

• PRIMARY KEY - primary key will be created in the table from a column value;
• UNIQUE - unique index will be created in the table, whose key is the column value;
• INDEX - nonunique index will be created in the table, whose key is the column value;
• NOT NULL - the column may not have the NULL value (a special case of internal integrity constraint);
• REFERENCES - a referential integrity rule will be created in the table, that binds this column with a specified column in a superior table;
• CHECK - a integrity constraint specified with a condition is created in the table.

Column properties are a simplified form of simple table properties cases. They cannot be assigned names, therefore the server assigns names to them automatically.

An integrity constraint specified as a column property doesn't allow you to specify the time for the constraint check. The default setting specified by the compatibility attributes is applied. When it's desired to specify other than the default check time, you have to define the constraint for the table.

CREATE TABLE Statement

Database Tables

Database Table Indexes