Creates a new table in an existing database. Available in gpre, DSQL, and isql. Important To create a SQL Global Temporary table see: SQL global temporary tables in the Data
Definition Guide
Syntax CREATE TABLE table [EXTERNAL [FILE] 'filespec'] (col_def [, col_def | tconstraint …]);
col_def = col {datatype | COMPUTED [BY] (expr) | domain}
[DEFAULT {literal | NULL | USER}] [NOT NULL]
[col_constraint] [COLLATE collation]
datatype =
{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 datatype)
| 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 Chapter 13, “Encrypting Your Data” in the Data
Definition Guide.
datatype SQL datatype for the column; see “Datatypes” on page 2-6
COMPUTED [BY] (expr) Specifies that the value of the column’s data 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 datatypes 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
Description CREATE TABLE establishes a new table, its columns, and integrity constraints in an existing database. The user who creates a table is the table’s owner 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 datatype for data entered into the column.
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.
CONSTRAINTconstraint 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 Chapter 7, “Character Sets and Collation Orders” for more information
• Computed columns are based on an expression. Column values are computed each time the table is accessed. If the datatype 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 datatype 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 Chapter 7, “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 datatypes. Choice of collation order is restricted to those supported for the column’s given character set, 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 datatype definition. For a complete list of collation orders recognized by InterBase, see Chapter 7, “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:
• 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.
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.
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 T1’s primary key P1. 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);
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 database’s default character set. 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); See also CREATE DOMAIN, DECLARE TABLE, GRANT, REVOKE
For more information on creating metadata, using integrity constraints, external tables, datatypes, collation order, and character sets, see the Data Definition Guide.
For detailed information on encryption and decryption, see the topics “Encrypting Data” (page 13-9) and “Decrypting Data” (page 13-11) in the Data Definition Guide.