CREATE TABLE Statement

Create a new SQL table, defining its columns and constraints.

Syntax

CREATE TABLE base-table-name ( table-element {,table-element{...}} )

Syntax Elements

base-table-name =
{owner-name.}base-table-identifier

owner-name
The SQL user-id of the creator of the table.

base-table-identifier
Any user-defined name (except a Reserved Word), starting with a letter, comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

table-element =
column-definition || table-constraint-definition

column-definition =
column-identifier data-type {column-constraint-definition{ column-constraint-definition}...}

column-identifier
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

data-type
Refer to supported data types section.

column-constraint-definition =
{ CONSTRAINT constraint-name }
    [ NOT NULL || PRIMARY KEY || UNIQUE || references-definition ]

references-definition =
REFERENCES base-table-name ( column-identifier )

table-constraint-definition =
{ CONSTRAINT constraint-id }
    [ UNIQUE (column-identifier {, column-identifier {...}} )
    || PRIMARY KEY (column-identifier {, column-identifier {...}} )
    || FOREIGN KEY (column-identifier {, column-identifier {...}} )
        REFERENCES (column-identifier {, column-identifier {...}} ) ]

Comments

Underlying Reality File

The SQL CREATE TABLE statement creates an underlying Reality file with modulo/ separation of 1,1 101,1. This file stores the SQL data.

File Name

The name of the underlying Reality file comprises the SQL user-id, un0der which the table was created, and the table name, separated by a period "." character. If a Reality file which matches this name already exists, a numeric suffix is appended to make the filename unique.

For example, if the user with id daveh, enters the statement:

CREATE TABLE EMP (EMPNO INTEGER PRIMARY KEY)

The Reality file daveh.EMP is created, unless this file already exists, in which case the file daveh.EMP_1, daveh.EMP_2, or daveh.EMP_3, etc. is created.

Privileges

The newly created table has owner privileges for the SQL user-id that created it and no privileges for any other user. The global update flag is set to allow updates, unlike a table created through SQLM which has the global update flag unset.

Assigning a Primary Key

An SQL table created without a PRIMARY KEY constraint is not updateable, since the primary key is mapped to the Reality file's item id. Because of this, the maximum length of a primary key is 98 characters, which includes any separation characters used in multi-column primary keys (the current separation character is a single tab character).

There are three ways that a primary key can be assigned to an SQL table:

1. Define it explicitly in the CREATE TABLE statement.

   • Using column-constraint-definitions. For example:

     CREATE TABLE EMP (EMPNO INTEGER PRIMARY KEY, ENAME VARCHAR(12))

   • Using table-constraint-definitions. For example:

     CREATE TABLE EMP (EMPNO INTEGER, ENAME VARCHAR(12), PRIMARY KEY (EMPNO))

     or

     CREATE TABLE EMP (EMPNO INTEGER, DEPTNO INTEGER, ENAME VARCHAR(12), PRIMARY KEY (EMPNO,DEPTNO)) 

2. Inferred by the CREATE TABLE statement. Any column(s) that has a UNIQUE and NOT NULL column-constraint-definition in a CREATE TABLE statement that does not have a PRIMARY KEY constraint will be made the primary key.

   • Using column-constraint-definition. For example:

     CREATE TABLE EMP (EMPNO INTEGER UNIQUE NOT NULL, ENAME VARCHAR(12))

     and

     CREATE TABLE EMP (EMPNO INTEGER UNIQUE NOT NULL, ENAME VARCHAR(12) UNIQUE NOT NULL)

     the first UNIQUE and NOT NULL column found will become the primary key; that is, EMPNO.

   • Using table-constraint-definitions. For example

     CREATE TABLE EMP (EMPNO INTEGER NOT NULL, ENAME VARCHAR(12), UNIQUE (EMPNO)) 

     CREATE TABLE EMP (EMPNO INTEGER NOT NULL, ENAME VARCHAR(12)NOT NULL, UNIQUE (EMPNO,ENAME))

     In the second case a multi-column primary key will be allocated comprising both EMPNO and ENAME columns. This is because the UNIQUE constraint in this case applies only to the two columns in combination, not to either of them individually.

     Note

     NOT NULL can only be specified as a column constraint.

3. If a table is created without a primary key constraint but a unique index is created for that table, the index column or columns will become the primary key; for example:

   • Entering the command

     CREATE TABLE EMP (EMPNO INTEGER, ENAME VARCHAR(12))

     followed by

     CREATE UNIQUE INDEX RB1 ON EMP (EMPNO ASC)

     makes the column EMPNO the primary key.

   or

   • Entering the command

     CREATE TABLE EMP (EMPNO INTEGER, ENAME VARCHAR(12))

     followed by

     CREATE UNIQUE INDEX RB1 ON EMP (EMPNO,ENAME ASC)

     makes the columns EMPNO and ENAME a multi-column primary key.

4. If a table does not have a primary key, ALTER TABLE can be used to define the primary key, provided the table is empty. For example:

   CREATE TABLE EMP (EMPNO INTEGER, ENAME VARCHAR(12))

   followed by

   ALTER TABLE EMP ADD PRIMARY KEY (EMPNO, ENAME)

   The columns EMPNO and ENAME will now become a multi-column primary key.

Foreign Key Constraint

In a FOREIGN KEY constraint, the number of columns in the candidate key must match the number in the foreign key.

Examples

CREATE TABLE EMP (EMPNO INTEGER PRIMARY KEY, COMM DECIMAL(7,2), DEPTNO INTEGER, ENAME CHAR(10) NOT NULL, HIREDATE DATE, MGR INTEGER, SAL DECIMAL (7,2), TITLE VARCHAR(9))

CREATE TABLE EMP_DETAILS (EMPNO INTEGER PRIMARY KEY, DETAILS LONGVARCHAR(2000), EXTN SMALLINT)