skip to main content
SQL statements : DDL statements : CREATE TABLE
 

CREATE TABLE

This statement creates a relational table.
To support CREATE TABLE processing, the IP GETSUPPORT operation must return TRUE for IP_SUPPORT_CREATE_TABLE.
Tables are created with no records. You can add rows to a table with the INSERT statement. After creating a table, you can define additional columns, and integrity constraints with the ADD clause of the ALTER TABLE statement. You can change the definition of an existing column with the ALTER COLUMN clause of the ALTER TABLE statement.

Syntax 

CREATE TABLE table_name (column-element, column element...)
column-element = column-definition
| [CONSTRAINT constraint-name] table-constraint-definition
 
column-definition = column-identifier data-type
[DEFAULT default-value]
[column-constraint [column-constraint]...]
 
data-type = INTEGER | SMALLINT
| DECIMAL [(<integer-literal> [,<integer-literal>])]
| NUMERIC [(<integer-literal> [,<integer-literal>])]
| FLOAT [( <integer-literal> )]
| REAL [( <integer-literal> )]
| DOUBLE | DOUBLE PRECISION
| CHAR [( <integer-literal> )]
| VARCHAR [( <integer-literal> )]
| DATE | TIME | TIMESTAMP | LONGVARCHAR
| BINARY | VARBINARY
| LONGVARBINARY [( <integer-literal> )]
| WCHAR [( <integer-literal> )]
| WVARCHAR [( <integer-literal> )]
| LONG
| BIT
| TINYINT
| WLONGVARCHAR
| BIGINT
 
column-constraint = NOT NULL
| NULL
| UNIQUE [KEY]
| PRIMARY KEY
| REFERENCES pkey-table-name [pkey-column-identifier]
| USERDATA 'literal string'
 
table-constraint-definition = UNIQUE [KEY]
(column-identifier [,column-identifier]...)
| PRIMARY KEY (column-identifier [,column-identifier]...)
| FOREIGN KEY (column-identifier [,column-identifier]...)
REFERENCES pkey-table-name
(pkey-column-identifier [,pkey-column-identifier]...)
| NESTED KEY (column-identifier [,column-identifier]...)
REFERENCES pkey-table-name

Example 

CREATE TABLE t1 (c1 integer UNIQUE KEY PRIMARY KEY);
CREATE TABLE emp (empno numeric(5) NOT NULL PRIMARY KEY, name char(20),
hiredate timestamp, sal float USERDATA 'formula ''hr*wage''',
deptno decimal(5,2) REFERENCES dept (deptno));