Creating a Table (Embedded SQL Guide)
Go Up to Creating Metadata
The CREATE TABLE statement defines a new database table and the columns and integrity constraints within that table. Each column can include a character set specification and a collation order specification. CREATE TABLE also automatically imposes a default SQL security scheme on the table. The person who creates a table becomes its owner. An owner of a table is assigned all privileges for it, including the right to grant privileges to other users.
A table can be created only for a database that already exists. At its simplest, the syntax for CREATE TABLE is as follows:
EXEC SQL CREATE TABLE name (<col_def> | <table_constraint> [, <col_def> | <table_constraint> ...]);
<col_def> defines a column using the following syntax:
<col> {<data_type> | COMPUTED [BY] (<expr>) | domain}
[DEFAULT {literal | NULL | USER}]
[NOT NULL]
[<col_constraint>]
[COLLATE collation]
<col> must be a column name unique within the table definition.
<data_type> specifies the SQL data type to use for column entries. COMPUTED BY can be used to define a column whose value is computed from an expression when the column is accessed at run time.
<col_constraint> is an optional integrity constraint that applies only to the associated column.
<table_constraint> is an optional integrity constraint that applies to an entire table.
Integrity constraints ensure that data entered in a table meets specific requirements, to specify that data entered in a table or column is unique, or to enforce referential integrity with other tables in the database.
A column definition can include a default value. Some examples:
stringfld VARCHAR(10) DEFAULT ‘abc’ integerfld INTEGER DEFAULT 1 numfld NUMERIC(15,4) DEFAULT 1.5 datefld1 DATE DEFAULT ‘2/01/2001’ datefld2 DATE DEFAULT ‘TODAY’ userfld VARCHAR(12) DEFAULT USER
The last two lines show special InterBase features: ‘TODAY’ defaults to the current date, and USER is the user who is performing an insert to the column.
The following code fragment contains SQL statements that create a database, employee.ib, and create a table, EMPLOYEE_PROJECT, with three columns, EMP_NO, PROJ_ID, and DUTIES:
EXEC SQL CREATE DATABASE 'employee.ib'; EXEC SQL CREATE TABLE EMPLOYEE_PROJECT ( EMP_NO SMALLINT NOT NULL, PROJ_ID CHAR(5) NOT NULL, DUTIES Blob SUB_TYPE 1 SEGMENT SIZE 240 ); EXEC SQL COMMIT;
An application can create multiple tables, but duplicating an existing table name is not permitted.
For more information about SQL data types and integrity constraints, see the Data Definition Guide. For more information about CREATE TABLE syntax, see the Language Reference Guide. For more information about changing or assigning table privileges, see “Planning Security” in the Data Definition Guide.
Creating a Computed Column
A computed column is one whose value is calculated when the column is accessed at run time. The value can be derived from any valid SQL expression that results in a single, non-array value. Computed columns are “virtual” in that they do not correspond to data that is physically stored in the database. The values are always generated during the SELECT query. They have to be generated dynamically in case the values they are based on change.
To create a computed column, use the following column declaration syntax in CREATE TABLE:
col COMPUTED [BY] (<expr>)
The expression can reference previously defined columns in the table. For example, the following statement creates a computed column, FULL_NAME, by concatenating two other columns, LAST_NAME, and FIRST_NAME:
EXEC SQL CREATE TABLE EMPLOYEE ( . . . FIRST_NAME VARCHAR(10) NOT NULL, LAST_NAME VARCHAR(15) NOT NULL, . . . FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME) );
For more information about COMPUTED BY, see the Data Definition Guide.
Declaring and Creating a Table
In programs that mix data definition and data manipulation, the DECLARE TABLE statement must be used to describe a structure of a table to the InterBase preprocessor, gpre, before that table can be created. During preprocessing, if gpre encounters a DECLARE TABLE statement, it stores the description of a table for later reference. When gpre encounters a CREATE TABLE statement for the previously declared table, it verifies that the column descriptions in the CREATE statement match those in the DECLARE statement. If they do not match, gpre reports the errors and cancels preprocessing so that the error can be fixed.
When used, DECLARE TABLE must come before the CREATE TABLE statement it describes. For example, the following code fragment declares a table,
EMPLOYEE_PROJ, then creates it:
EXEC SQL DECLARE EMPLOYEE_PROJECT TABLE ( EMP_NO SMALLINT, PROJ_ID CHAR(5), DUTIES Blob(240, 1) ); EXEC SQL CREATE TABLE EMPLOYEE_PROJECT ( EMP_NO SMALLINT, PROJ_ID CHAR(5), DUTIES Blob(240, 1) ); EXEC SQL COMMIT;
For more information about DECLARE TABLE, see the Language Reference Guide.