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> {<datatype> | 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.
<datatype> 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.