CREATE TABLE

Go Up to Statement and Function Reference (Language Reference Guide)

Creates a new table in an existing database. Available in gpre, DSQL, and isql.

Important:
To create a global Temporary table, see: “global Temporary Tables” in the Data Definition Guide.

CREATE TABLE <table> [EXTERNAL [FILE] '<filespec>']
(col_def [, col_def | tconstraint …]) [ON COMMIT {PRESERVE | DELETE} ROWS] [[NO] RESERVE SPACE];

col_def = col {data_type | COMPUTED [BY] (expr) | domain}
[DEFAULT {literal | NULL | USER}]
[NOT NULL]
[col_constraint]
[COLLATE collation]
data_type =
{SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}[array_dim]
| (DATE | TIME | TIMESTAMP} [array_dim]
| {DECIMAL | NUMERIC} [(precision [, scale])] [array_dim]
| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
[array_dim] [CHARACTER SET charname]
| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}
[VARYING] [(int)] [array_dim]
| BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]
[CHARACTER SET charname]
| BLOB [(seglen [, subtype])]
| BOOLEAN
array_dim = [[x:]y [, [x:]y …]]
expr = A valid SQL expression that results in a single value.
col_constraint = [CONSTRAINT constraint]
{ UNIQUE
| PRIMARY KEY
| REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK (search_condition)}
tconstraint = [CONSTRAINT constraint]
{{PRIMARY KEY | UNIQUE} (col [, col …])
| FOREIGN KEY (col [, col …])
REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK (search_condition)}
search_condition = val operator {val | (select_one)}
| val [NOT] BETWEEN val AND val
| val [NOT] LIKE val [ESCAPE val]
| val [NOT] IN (val [, val …] | select_list)
| val IS [NOT] NULL
| val {>= | <=}
| val [NOT] {= | < | >}
| {ALL | SOME | ANY} (select_list)
| EXISTS (select_expr)
| SINGULAR (select_expr)
| val [NOT] CONTAINING val
| val [NOT] STARTING [WITH] val
| (search_condition)
| NOT search_condition
| search_condition OR search_condition
| search_condition AND search_condition
val = { col [array_dim] | :variable
| constant | expr | function
| udf ([val [, val …]])
| NULL | USER | RDB$DB_KEY | ? }
[COLLATE collation]
constant = num | 'string' | charsetname 'string'
function = COUNT (* | [ALL] val | DISTINCT val)
| SUM ([ALL] val | DISTINCT val)
| AVG ([ALL] val | DISTINCT val)
| MAX ([ALL] val | DISTINCT val)
| MIN ([ALL] val | DISTINCT val)
| CAST (val AS data_type)
| UPPER (val)
| GEN_ID (generator, val)
operator = {= | < | > | <= | >= | !< | !> | <> | !=}
select_one = SELECT on a single column; returns exactly one value.
select_list = SELECT on a single column; returns zero or more values.
select_expr = SELECT on a list of values; returns zero or more values.

Important:
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Notes on the CREATE TABLE statement:

When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is 6 characters long:

my_array VARCHAR(6)[5,5]

Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of integers that begins at 10 and ends at 20:

my_array INTEGER[10:20]

In SQL and isql, you cannot use val as a parameter placeholder (like “?”).
In DSQL and isql, val cannot be a variable.
You cannot specify a COLLATE clause for Blob columns.
expr is any complex SQL statement or equation that produces a single value.

Argument	Description
<table>	Name for the table; must be unique among table and procedure names in the database.
`EXTERNAL[FILE]`‘<filespec>’.	Declares that data for the table under creation resides in a table or file outside the database; <filespec> is the complete file specification of the external file or table.
<col>	Name for the table column; unique among column names in the table. You can also encrypt/decrypt a column when you create a table. For instructions on how to encrypt and decrypt a column or database see “Encrypting Your Data” in the Data Definition Guide.
<data_type>	SQL data type for the column; see Data Types (Language Reference Guide).
`COMPUTED[BY]`(<expr>)	Specifies that the value of the data of the coulmn is calculated from <expr> at runtime and is therefore not allocated storage space in the database. <expr> can be any arithmetic expression valid for the data types in the expression. Any columns referenced in <expr> must exist before they can be used in <expr>. <expr> cannot reference Blob columns. <expr> must return a single value, and cannot return an array.
<domain>	Name of an existing domain
`DEFAULT`	Specifies a default column value that is entered when no other entry is made; possible values are: <literal>: Inserts a specified string, numeric value, or date value. `NULL`: Enters a `NULL` value. `USER`: Enters the user name of the current user. Column must be of compatible text type to use the default. Defaults set at column level override defaults set at the domain level.
`CONSTRAINT` <constraint>	Name of a column or table constraint; the constraint name must be unique within the table.
<constraint_def>	Specifies the kind of column constraint; valid options are `UNIQUE`, `PRIMARY KEY`, `CHECK`, and `REFERENCES`.
`REFERENCES`	Specifies that the column values are derived from column values in another table; if you do not specify column names, InterBase looks for a column with the same name as the referencing column in the referenced table.
`ON DELETE\|ON UPDATE`	Used with `REFERENCES`: Changes a foreign key whenever the referenced primary key changes; valid options are: [Default] `NO ACTION`: Does not change the foreign key; may cause the primary key update to fail due to referential integrity checks. `CASCADE`: For `ON DELETE`, deletes the corresponding foreign key; for `ON UPDATE`, updates the corresponding foreign key to the new value of the primary key. `SET NULL`: Sets all the columns of the corresponding foreign key to `NULL`. `SET DEFAULT`: Sets every column of the corresponding foreign key is set to its default value in effect when the referential integrity constraint is defined. When the default for a foreign column changes after the referential integrity constraint is defined, the change does not have an effect on the default value used in the referential integrity constraint.
`CHECK` <search_condition>	An attempt to enter a new value in the column fails if the value does not meet the <search_condition>.
`COLLATE` <collation>	Establishes a default sorting behavior for the column; see Character Sets and Collation Orders for more information.

Description: CREATE TABLE establishes a new table, its columns, and integrity constraints in an existing database. The user who creates a table is the owner of the table and has all privileges for it, including the ability to GRANT privileges to other users, triggers, and stored procedures.

CREATE TABLE supports several options for defining columns:

Local columns specify the name and data type for data entered into the column.
Computed columns are based on an expression. Column values are computed each time the table is accessed. If the data type is not specified, InterBase calculates an appropriate one. Columns referenced in the expression must exist before the column can be defined.
Domain-based columns inherit all the characteristics of a domain, but the column definition can include a new default value, a NOT NULL attribute, additional CHECK constraints, or a collation clause that overrides the domain definition. It can also include additional column constraints.
The data type specification for a CHAR, VARCHAR, or Blob text column definition can include a CHARACTER SET clause to specify a particular character set for the single column. Otherwise, the column uses the default database character set. If the database character set is changed, all columns subsequently defined have the new character set, but existing columns are not affected. For a complete list of character sets recognized by InterBase, see Character Sets and Collation Orders.
If you do not specify a default character set, the character set defaults to NONE. Using character set NONE means that there is no character set assumption for columns; data is stored and retrieved just as you originally entered it. You can load any character set into a column defined with NONE, but you cannot load that same data into another column that has been defined with a different character set. In this case, no transliteration is performed between the source and destination character sets, and errors may occur during assignment.
The COLLATE clause enables specification of a particular collation order for CHAR, VARCHAR, and Blob text data types. Choice of collation order is restricted to those supported for the given character set of the column, which is either the default character set for the entire database, or a different set defined in the CHARACTER SET clause as part of the data type definition. For a complete list of collation orders recognized by InterBase, see Character Sets and Collation Orders.

NOT NULL is an attribute that prevents the entry of NULL or unknown values in column. NOT NULL affects all INSERT and UPDATE operations on a column.

Important:
A DECLARE TABLE must precede CREATE TABLE in embedded applications if the same SQL program both creates a table and inserts data in the table.

The EXTERNAL FILE option creates a table whose data resides in an external file, rather than in the InterBase database. Use this option to:

Define an InterBase table composed of data from an external source, such as data in files managed by other operating systems or in non-database applications.
Transfer data to an existing InterBase table from an external file.

External files must either be placed in <InterBase_home>/ext or their location must be specified in the ibconfig configuration file using the EXTERNAL_FILE_DIRECTORY entry.

Referential integrity constraints:

You can define integrity constraints at the time you create a table. These constraints are rules that validate data entries by enforcing column-to-table and table-to-table relationships. They span all transactions that access the database and are automatically maintained by the system. CREATE TABLE supports the following integrity constraints:
A PRIMARY KEY is one or more columns whose collective contents are guaranteed to be unique. A PRIMARY KEY column must also define the NOT NULL attribute. A table can have only one primary key.
UNIQUE keys ensure that no two rows have the same value for a specified column or ordered set of columns. A unique column must also define the NOT NULL attribute. A table can have one or more UNIQUE keys. A UNIQUE key can be referenced by a FOREIGN KEY in another table.
Referential constraints (REFERENCES) ensure that values in the specified columns (known as the foreign key) are the same as values in the referenced UNIQUE or PRIMARY KEY columns in another table. The UNIQUE or PRIMARY KEY columns in the referenced table must be defined before the REFERENCES constraint is added to the secondary table. REFERENCES has ON DELETE and ON UPDATE clauses that define the action on the foreign key when the referenced primary key is updated or deleted. The values for ON UPDATE and ON DELETE are as follows:

Action specified	Effect on foreign key
`NO ACTION`	[Default] The foreign key does not change. This may cause the primary key update or delete to fail due to referential integrity checks.
`CASCADE`	The corresponding foreign key is updated or deleted as appropriate to the new value of the primary key.
`SET DEFAULT`	Every column of the corresponding foreign key is set to its default value. If the default value of the foreign key is not found in the primary key, the update or delete on the primary key fails. The default value is the one in effect when the referential integrity constraint was defined. When the default for a foreign key column is changed after the referential integrity constraint is set up, the change does not have an effect on the default value used in the referential integrity constraint.
`SET NULL`	Every column of the corresponding foreign key is set to `NULL`.

You can create a FOREIGN KEY reference to a table that is owned by someone else only if that owner has explicitly granted you REFERENCES privilege on that table. Any user who updates your foreign key table must have REFERENCES or SELECT privileges on the referenced primary key table.
CHECK constraints enforce a <search_condition> that must be true for inserts or updates to the specified table. <search_condition> can require a combination or range of values or can compare the value entered with data in other columns.

Note:
Specifying USER as the value for a <search_condition> references the login of the user who is attempting to write to the referenced table.

Creating PRIMARY KEY and FOREIGN KEY constraints requires exclusive access to the database.
For unnamed constraints, the system assigns a unique constraint name stored in the RDB$RELATION_CONSTRAINTS system table.

Note:
Constraints are not enforced on expressions.

Examples: The following isql statement creates a simple table with a PRIMARY KEY:

CREATE TABLE COUNTRY (COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
CURRENCY VARCHAR(10) NOT NULL);

The next isql statement creates both a column-level and a table-level UNIQUE constraint:

CREATE TABLE STOCK (
MODEL SMALLINT NOT NULL UNIQUE,
MODELNAME CHAR(10) NOT NULL,
ITEMID INTEGER NOT NULL,
CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID));

The following isql statement illustrates table-level PRIMARY KEY, FOREIGN KEY, and CHECK constraints. The PRIMARY KEY constraint is based on three columns. This example also illustrates creating an array column of VARCHAR.

CREATE TABLE JOB (
JOB_CODE JOBCODE NOT NULL,
JOB_GRADE JOBGRADE NOT NULL,
JOB_COUNTRY COUNTRYNAME NOT NULL,
JOB_TITLE VARCHAR(25) NOT NULL,
MIN_SALARY SALARY NOT NULL,
MAX_SALARY SALARY NOT NULL,
JOB_REQUIREMENT BLOB(400,1),
LANGUAGE_REQ VARCHAR(15) [5],
PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY),
FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY),
CHECK (MIN_SALARY < MAX_SALARY));

In the next example, the F2 column in table T2 is a foreign key that references table T1 through the primary key P1 of T1. When a row in T1 changes, that change propagates to all affected rows in table T2. When a row in T1 is deleted, all affected rows in the F2 column of table T2 are set to NULL.

CREATE TABLE T1 (P1 INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE T2 (F2 INTEGER FOREIGN KEY (F2) REFERENCES T1 (P1)
ON UPDATE CASCADE
ON DELETE SET NULL);

The next isql statement creates a table with a calculated column:

CREATE TABLE SALARY_HISTORY (
EMP_NO EMPNO NOT NULL,
CHANGE_DATE DATE DEFAULT 'NOW' NOT NULL,
UPDATER_ID VARCHAR(20) NOT NULL,
OLD_SALARY SALARY NOT NULL,
PERCENT_CHANGE DOUBLE PRECISION
DEFAULT 0
NOT NULL
CHECK (PERCENT_CHANGE BETWEEN -50 AND 50),
NEW_SALARY COMPUTED BY
(OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO));

In the following isql statement the first column retains the default collating order for the default character set of the dataset. The second column has a different collating order, and the third column definition includes a character set and a collating order.

CREATE TABLE BOOKADVANCE (
BOOKNO CHAR(6),
TITLE CHAR(50) COLLATE ISO8859_1,
EUROPUB CHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR);

NO RESERVE SPACE for Database and User Tables

This feature is useful if you have very, large databases (VLDB) with tables that are archival in nature. An archival table means that the rows of a table are infrequently or never UPDATED or DELETED; have complex queries, such as aggregates and analytics that process a high percentage of rows; and where indexes are rebuilt and the database is backed and/or restored frequently. These database operations could see a performance improve of 20% or more with a savings in storage space.

By default, InterBase reserves a small amount of space in each data page of a table to optimize UPDATE and DELETE operations on resident rows. This reserve space can amount to 20% or more of the total space occupied by all of the rows of the table. Some tables archive historical data or data that are UPDATED infrequently or not at all and their rows may never be deleted. Database operations that process most or all of the rows, such as backup, restore, index creation, aggregate computation have always suffered performance penalties proportional to this reservation overhead.

For this reason, a CREATE/ALTER TABLE clause is introduced that prevents space reservation and maximizes row packing for the most efficient fill ratio. At the database level, it has been possible to restore a database with the -USE_ALL_SPACE switch so that no space is reserved for any table. To change the storage behavior in a like manner for new or existing databases, the same clause is introduced for CREATE/ALTER DATABASE.

User Interface To effect the new storage behavior, a non-standard SQL clause is added:

Clause is presented before the secondary file specification.

CREATE DATABASE <file name> ... [NO] RESERVE SPACE

Clause is presented after the column list specification and optional ON COMMIT clause for temporary tables.

CREATE TABLE <table name> ... [NO] RESERVE SPACE

This causes newly INSERTED rows to not reserve space on their data page for a DELETE record version stub, as would normally be the case. Over many row insertions, a decrease in storage size should be observed relative to what the table size would be in the absence of this feature. The optional NO keyword when used with ALTER TABLE toggles the behavior to the alternate state of the current storage behavior for the table.

The NO RESERVE storage modifier is preserved across database backup and restore. This state is stored as flag bit 64 (0x100) of RDB$RELATIONS.RDB$FLAGS for the user's table entry in the system table RDB$RELATIONS.

The clause is displayed by ISQL's SHOW TABLE command following the enumeration of a table's column definitions. It is also visible using ISQL's Extract (-x) command in a syntax-correct manner for the CREATE TABLE output of the respective table listing. The state for database-wide storage behavior is stored in a like manner for the RDB$DATABASE entry in RDB$RELATIONS.

ON COMMIT

A global temporary table is declared to a database schema via the normal CREATE TABLE statement with the following syntax:

CREATE GLOBAL TEMPORARY TABLE {{Placeholder|table}}
({{Placeholder|<col_def>}} [, {{Placeholder|<col_def>}} | {{Placeholder|<tconstraint>}} ...])<br/> [ON COMMIT {PRESERVE | DELETE} ROWS];

The first argument that you supply CREATE GLOBAL TEMPORARY TABLE is the temporary table name, which is required and must be unique among all table and procedure names in the database. You must also supply at least one column definition.

The ON COMMIT clause describes whether the rows of the temporary table are deleted on each transaction commit (ON COMMIT DELETE) or are left in place (ON COMMIT PRESERVE) to be used by other transactions in the same database attachment. If the ON COMMIT is not specified then the default behavior is to DELETE ROWS on transaction commit.

There is a change in behavior in the GLOBAL TEMPORARY TABLE Support with the InterBase XE3U2 release. When an SQL script is executed ISQL reported a "deadlock" if EXIT is called without COMMIT/ROLLBACK on a global temporary table. To resolve this issue, the GLOBAL TEMPORARY TABLES function has been redesigned which changes the behavior and corrects the deadlock error.

It is no longer possible for transactions emanating from the same connection to see each other's rows in a transaction-specific (ON COMMIT DELETE) temporary table. To do that, you must use a session-specific (ON COMMIT PRESERVE) temporary table that makes all rows visible to transactions starting in the same session. This is still not the same in that the rows will persist until the connection is finished.

A Global temporary table is dropped from a database schema using the normal DROP TABLE statement.

Advance To:

CREATE TRIGGER

CREATE TABLE

See Also

Advance To: