CREATE TABLE

SQL Development

OpenEdge SQL Data Definition Language : Using Data Definition Language statements : Working with tables : CREATE TABLE

CREATE TABLE

The CREATE TABLE statement allows you to create a new table in an existing database by defining its column names and column data types. Optionally, you can include table and column constraints.

The CREATE TABLE syntax forms are explained below:

The first syntax form explicitly specifies the definition of a column.

The second syntax form, with the ASquery_expression clause, implicitly defines the columns using the columns in a query expression.

The third syntax form defines the new table as a multi-tenant table, and allocates storage area of a database to the tenants.

The fourth syntax form specifies partition key details along with area definitions.

Note: The CREATE TABLE statement used for partitioned tables is an online operation. For detailed information on using the CREATE TABLE statement for partitioned tables, see OpenEdge Data Management: SQL Reference.

Syntax

The CREATE TABLE statement uses the following syntax:

CREATE TABLE [owner_name]table_name ( {column_definition|table_constraint}, ... )
[ AREA area_name]
    [ ENCRYPT WITH cipher]
    [BUFFER_POOL{PRIMARY|ALTERNATE}]
    [progress_table_attribute_keyword value ] ;
CREATE TABLE [owner_name]table_name[ (column_name[ NOT NULL] , ... ) ]
[ AREA area_name]    [ ENCRYPT WITH cipher]
    [BUFFER_POOL{PRIMARY|ALTERNATE}]
AS query_expression ;
CREATE TABLE
[owner_name]table_name ( {column_definition|table_constraint}, ... )
[ AREA area_name]
    [ ENCRYPT WITH cipher]
    [BUFFER_POOL{PRIMARY|ALTERNATE}]
    [progress_table_attribute_keyword value ]MULTI_TENANT
    [ FOR TENANT {tenant_name_1|DEFAULT}
    [ USING TABLE AREA table_area_name]
[ USING INDEX AREA index_area_name]
[ USING LOB AREA lob_area_name]    ]...
  [ FOR TENANT {tenant_name_2|DEFAULT}[ USING NO SPACE ]];
CREATE TABLE ...
[PARTITION BY {RANGE|LIST}column_name[PRO_DESCRIPTION desc]
(SUBPARTITION BY {RANGE|LIST}column_name[PRO_DESCRIPTION desc],...
area spec(partition attribute [partition attribute ], ...)

The following syntax is used to define an LOB column in CREATE TABLE statement:

{ LVARCHAR | CLOB | LVARBINARY | BLOB }[ ( length ) ]
[ AREA area_name][ ENCRYPT WITH cipher]
[BUFFER_POOL{PRIMARY|ALTERNATE}]

For details on using the CREATE TABLE statement to designate objects for buffer pool assignments, including an alternate buffer pool, see OpenEdge Data Management: Database Administration.

For details on using the CREATE TABLE statement to enable transparent data encryption, see OpenEdge Getting Started: Core Business Services - Security and Auditing.

Example: CREATE TABLE statement

The following example illustrates a CREATE TABLE statement. The cust_no column has the column constraint NOT NULL, which indicates that no row in the customer table is to have a NULL value in the cust_no column.

CREATE TABLE SPORTS.Customer
(
cust_no INTEGER NOT NULL,
last_name CHAR (30),
street CHAR (30),
city CHAR (20),
state CHAR (2)
) ;

The CREATE TABLE statement also allows you to specify the DEFAULT clause along with a column definition. The DEFAULT clause identifies the default value to be used for a column.

The default clause uses the following syntax:

column_name data_type
[ DEFAULT {literal| NULL | SYSDATE | SYSTIME | SYSTIMESTAMP }]
[column_constraint[column_constraint , ...]]

Example: CREATE TABLE statement with DEFAULT clause

The following CREATE TABLE statement shows how to use the DEFAULT clause. The following example sets a default value of 10 for the deptno column.

CREATE TABLE employee
(
empno INTEGER NOT NULL,
deptno INTEGER DEFAULT 10
) ;

For more information on the CREATE TABLE statement and the DEFAULT clause, see OpenEdge Data Management: SQL Reference.

Example: Creating a multi-tenant table

The following example shows how to create a multi-tenant table that overrides areas of the selected tenant table partition:

CREATE TABLE pub.acct_payable (acct_num integer, debit_date date)
  MULTI_TENANT
    FOR TENANT Consolidated_Freightways
USING TABLE AREA "CF Financial area"
USING INDEX AREA "CF Fin idx area"
    FOR TENANT "Mega Media Networks"
USING TABLE AREA "MMN Data area"
USING INDEX AREA "MMN index area"
USING LOB AREA "MMN pix area" ;
    FOR TENANT DEFAULT USING NO SPACE ;

Example: Creating a multi-tenant table with default areas

The following example shows how to a create multi-tenant table which uses the default area for all the table partition of a tenant. In this case, the DEFAULT tenant is allocated no space in the database storage area:

CREATE TABLE pub.mtcustomer (cust_num integer, hire_date date)
MULTI_TENANT;

Example: Defining areas for a DEFAULT tenant

The following example shows how to create a multi-tenant table which uses the default area for all areas tenants except the DEFAULT tenant:

CREATE TABLE pub.mtcustomer (cust_num integer, hire_date date)
MULTI_TENANT
FOR TENANT DEFAULT USING TABLE AREA "Scratch_Data_Area"
USING INDEX AREA "Misc_Index_Area";

Example: Creating a partitioned table

The following example illustrates how to partition a table based on customer ID. It specifies the default table area for each partition. Values less than or equal to 1000 will be a part of PARTITION p1, values ranging from 1001 to 2000 will be a part of PARTITION p2, and values ranging from 2001 to 3000 will be a part of PARTITION p3. Using an INSERT statement to insert values greater than or equal to 3000 returns an error.

CREATE TABLE Pub.tpcustomer (
Custid int,
Custname VARCHAR (50),
join_date date,
salary int)
PARTITION BY RANGE custid (
PARTITION p1 VALUES <= (1000) USING TABLE AREA "area_p1",
PARTITION p2 VALUES <= (2000) USING TABLE AREA "area_p2",
PARTITION p3 VALUES <= (3000) USING TABLE AREA "area_plast");

Example: Subpartitioning a table

The following example illustrates subpartitioning and creates LIST-LIST-LIST partitions on the tporder_list table:

CREATE TABLE tporder_list (orderid integer,
Item varchar(50),
Order_date date,
Country varchar(50),
State varchar(50),
City varchar(50))PARTITION BY LIST (Country)SUBPARTITION BY LIST(State)SUBPARTITION BY LIST(City)USING TABLE AREA "order list table area"
(PARTITION USA_MA_BT VALUES IN (‘USA','MA','Boston'),
PARTITION USA_NY_NY VALUES IN
(‘USA','NY','Ney York'),
USING INDEX AREA "secunderabad index area"
PARTITION USA_MA_BD VALUES IN
(‘USA,'MA','Bedford'))

Example: Creating a table with LOB partitions

The following example illustrates how to create a table with LOB partitions:

CREATE TABLE Pub.tpcustomer (
F1 INTEGER,
F2 VARCHAR)
PARTITION BY RANGE F1 USING TABLE AREA "Tenant 1 table Area" (
PARTITION Pub.tpcustomer_p1 VALUES <= (1000)USING LOB AREA "Partn misc lob Area",
PARTITION Pub.tpcustomer_p2 VALUES <= (2000),
PARTITION Pub.tpcustomer_p3 VALUES <= (3000);

LOB partitions are created in areas as per the areas specified in the partition definition. In the above example, for LOB column F2, for partition Pub.tpcustomer_p1, the LOB partition area is Partn misc lob Area; For the rest of the partitions, the LOB partition area is Tenant 1 table Area.

Note: The AREA phrase is not allowed for LOB columns while creating partitioned tables and the LOB columns cannot be partition key columns.

For more information on the CREATE TABLE statement, see OpenEdge Data Management: SQL Reference.