SQL Reference

SQL Reference : OpenEdge SQL Statements : CREATE TABLE

CREATE TABLE

Creates a table definition. A table definition consists of a set of named column definitions for data values that will be stored in rows of the table. SQL provides two forms of the CREATE TABLE statement.

The CREATE TABLE syntax can be used to:

Explicitly specify the definition of a column.

Implicitly define the columns using the columns in a query expression with the ASquery_expression clause.

Define a new table as a multi-tenant table, and allocate storage area of a database to the tenants.

Specify partition key details along with area definitions.

Create a partitioned table with no partition schema defined

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 definition ];

CREATE [ PARTITION ] TABLE ...

Parameters

column_definition

Defines a column. The syntax for column_definition is given below:

column_namedata_type
[ COLLATE case_insensitive |case_sensitive ]
[ DEFAULT {literal| NULL | SYSDATE | SYSTIME | SYSTIMESTAMP }]
[column_constraint[column_constraint , ...]]
[progress_column_attribute_keyword value ], ...]

column_name data_type

Names a column and associates a data type to it. The name of the column must be different from other column names already defined in the table. The data_type must be supported by OpenEdge.

When a table contains more than one column, a comma separator should be used after each column_definition, except for the final column_definition.

COLLATE

Indicates the case sensitivity of the column. The default value for the COLLATE element is case_sensitive.

case_insensitive

Indicates that the column will be case-insensitive. The word case_insensitive itself cannot be used as a valid input. The value for the case_insensitive clause can only be _I, I, or the default database collation with the suffix _I (For example: COLLATE_I, COLLATE I,or COLLATE BASIC_I).

case_sensitive

Indicates that the column will be case-sensitive. The word case_sensitive itself cannot be used as a valid input. The value for the case_sensitive clause can only be _S, S, or the default database collation with the suffix _S (For example: COLLATE_S, COLLATE S,or COLLATE BASIC_S).

DEFAULT

Specifies an explicit default value for a column. The column takes on the default value if an INSERT statement does not include a value for the column. If a column definition omits the DEFAULT clause, NULL value gets assigned.

The DEFAULT clause accepts the arguments shown in the table below:

Argument	Description
literal	An integer, numeric, or a string constant.
NULL	A null value.
SYSDATE	Displays the current date. Valid only for columns defined with the DATE data type. SYSDATE is equivalent to the Progress default keyword TODAY. The argument represents a DATE value.
SYSTIME	Displays the current time. The argument represents a TIME value.
SYSTIMESTAMP	Displays the current date and time. The argument represents a TIMESTAMP value.

column_constraint

Specifies a constraint that is applied when you insert or update a value in the associated column.

progress_column_attribute_keyword value

ABL column attribute keyword and value.

The syntax used to define a LOB column is shown below:

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

table_constraint

Specifies a constraint that is applied when you insert or update a row in the table.

AREA area_name

Specifies the name of the storage area where data of the table is to be stored. The name of the storage area must be specified within double quotes.

If the specified area does not exist, the database returns an error. If you do not specify an area, the default area is used.

ENCRYPT WITH cipher

Allows the table to be encrypted by designating an appropriate cipher.

BUFFER_POOL {PRIMARY|ALTERNATE}

Allows the table to be assigned to a primary or alternate buffer pool.

progress_table_attribute_keyword value

ABL table attribute keyword and value.

AS query_expression

Specifies a query expression to be used for the data type and data values for the columns of the table. The type and length of the columns of the query_ expression result become the type and length of the respective columns in the table you created. The rows in the resultant set of the query_ expression are inserted into the table after its creation. The column names are optional in this form of the CREATE TABLE statement. If it is omitted, the names of the columns of the table are taken from the column names of the query_ expression.

MULTI_TENANT

Defines the table as a multi-tenant table. The MULTI_TENANT phrase can occur only after the column_definition and the progress_table_attribute_keyword, and before the AS query_expression part of the CREATE TABLE syntax.

FOR TENANT tenant_name

Specifies the name of a tenant. The FOR TENANT phrase can be repeated for as many existing tenants as required.

TABLE AREA area_name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the CREATE TABLE statement execution. Specifying the TABLE AREA is optional.

Specifies the name of the default storage area allocated for the tenant.

INDEX AREA index _area_name

If the storage area for an index is omitted in the syntax, then any index will be allocated in the table area.

To define INDEX AREA, you must define the TABLE AREA.

LOB AREA lob _area_name

If the storage area for a LOB is omitted in the syntax, then any LOB will be allocated in the table area.

To define the LOB AREA, you must define the TABLE AREA.

PRO_DESCRIPTION value

Allows you to enter ABL description. value is an arbitrary character string.

USING NO SPACE

Indicates that no space is allocated for index data storage in the table. This phrase is valid only if the tenant partition for the table is allocated NO SPACE.

Note: For a particular tenant, the table and the Index partitions must have the same allocation state.

partition definition

Uses the following syntax:

[PARTITION BY {RANGE|LIST} column_name
[PRO_DESCRIPTION desc]
(subpartition definition,...)area spec
(partition attribute[partition attribute], ...)

column_name

Uses the following syntax:

simple_column_name|(simple_column_name)

subpartition definition

Uses the following syntax:

SUBPARTITION BY {RANGE|LIST}column_name[PRO_DESCRIPTION desc]

area spec

Specifies the default areas for all partitions defined, if not overridden by specific partition definitions.

The syntax for area spec is:

(([ USING TABLE AREA area name]
[ USING INDEX AREA area name]
[ USING LOB AREA area name])
|[ USING NO SPACE ])

partition attribute

Uses the following syntax:

PARTITION
[partition_name] VALUES {<=|IN}(column_values)
[ RO_RW_ATTRIBUTE ][area spec][PRO_DESCRIPTION desc]

partition_name

Specifies the SQL identifier.

column_values

Uses the following syntax:

column_value[ , column_value, ...]

column_value

Uses a constant as its value

RO_RW_ATTRIBUTE

Uses the following syntax and marks the partition as a read-only partition or a read-write partition:

READ_ONLY | READ_WRITE

If RO_RW_Attribute is not specified, then by default, the partition is marked as a read-write partition.

A NO SPACE partition cannot be marked as a read-only partition.

Notes

Table columns defined in OpenEdge SQL have default format values identical to those created by the Data Dictionary. Thus, columns created by SQL will have the same default format as columns created by ABL tools.

The following semantics apply when using CREATE TABLE for partitioned tables:

The partitioned database objects must be defined only in Type II storage areas.

A database can be enabled for both multi-tenancy and table partitioning, but a table cannot be both multi-tenant and partitioned.

Expressions and scalar functions cannot be used as partition keys and key columns.

The PARTITION BY clause does not allow column lists.

Partition names are optional, unique across a database, and have the lexical properties of a table name; if not specified, system generated partition names are used.

The maximum number of partitions per table is 32,767.

A partition can be defined without any allocated data storage.

<= indicates that, for the partition being defined, the values of the partition key columns in data rows are less than or equal to the value specified for that partition in the CREATE TABLE statement. The partition keys values in data rows are greater than the values specified for the previous partition definition.

Partition keys must be literal constants and must be enclosed in parentheses for LIST partitions. For RANGE partitions, the parentheses are optional.

All index supported data types can be used as partition keys.

The existing AREA clause in the CREATE TABLE statement cannot be used if a PARTITION BY clause is used.

Descriptions can be added using the CREATE TABLE statement in the following two places:

After the PARTITION BY clause (as a description for all partitions)

At the end of each partition definition (as a description for that particular partition, overriding any description provided at upper levels)

The area definition at the lower level always takes priority among the area definitions provided at multiples levels.

All INSERT statements with partition key column values that match the values specified in the partition definition belong to the corresponding partition. If none of the partition definitions match the key column value in an INSERT statement, then that INSERT statement returns an error. For a LIST partition, the column values specified by an INSERT statement must match the partition key value. For a RANGE partition, the column values must be less than or equal to the highest partition key value defined. This ensures that the new table row maps to one of the defined partitions.

The partition policy name has the table name suffixed with the table ID. If the table name is more than 20 characters long, only the first 20 characters from the table name are used.

A partition key column cannot be dropped or renamed.

A column cannot be removed from the partition key definition of the partitioned table.

A column's datatype cannot be changed (applicable to any column; not just a partition key column).

The following semantics apply when using CREATE TABLE for subpartitioning:

Up to 15 levels of subpartitioning are allowed.

Partition key columns must not be repeated.

Parentheses around partition key columns are optional.

For a subpartition, all the leading subpartition levels must define LIST partitions. The last subpartition level can be either a RANGE partition or a LIST partition.

If a partition definition has a RANGE column then the <= clause must be used to specify partition key values, otherwise use the IN clause.

For RANGE partitions, all partitions having the same LIST key values must be specified sequentially with the RANGE key values in ascending order.

The partition definition must contain values for all partition keys.

Each column_value corresponds to the column name of the partition or subpartition, in the same order in which the column names are defined.

The following semantics apply when using CREATE TABLE to create a partitioned table with no partition schema defined:

The partition key columns and partition definitions must not be specified.

A default ROWID index is created for a partitioned table with no partition schema defined.

The AREA clause is optional; if specified, it must be a type II area.

The initial composite partition of the table and LOB columns are created with NO SPACE.

Examples

CREATE TABLE statement

The following example shows that the user who issues the CREATE TABLE statement must have REFERENCES privilege on the itemno column of the table john.item in CREATE TABLE supplier_item:

CREATE TABLE supplier_item
(
supp_no INTEGER NOT NULL PRIMARY KEY,
item_no INTEGER NOT NULL REFERENCES john.item ( itemno ),
qty INTEGER
) ;

CREATE TABLE statement

The table is created in the current owner schema.

The following CREATE TABLE statement explicitly specifies a table owner, gus:

CREATE TABLE gus.account
(
account integer,
balance numeric (12,2),
info char (84)) ;

CREATE TABLE statement to load a table with a subset of the data in another table

The following example shows the ASquery_expression form of CREATE TABLE to create and load a table with a subset of the data in the customer table:

CREATE TABLE dealer (name, street, city, state)
AS
SELECT name, street, city, state
FROM customer
WHERE state IN ('CA','NY', 'TX') ;

CREATE TABLE statement to include a column constraint

The following example includes a NOT NULL column constraint and DEFAULT clause for definition of a column:

CREATE TABLE emp
(
empno integer NOT NULL,
deptno integer DEFAULT 10,
join_date date DEFAULT NULL
) ;

CREATE TABLE statement to create a table with two columns

The following example shows how to create a table with two columns. Both of them have ABL descriptions and specified column labels:

CREATE TABLE emp
(
     empno INTEGER NOT NULL UNIQUE
       PRO_DESCRIPTION 'A unique number for each employee'
       PRO_COL_LABEL 'Employee No.'
     deptno INTEGER DEFAULT 21 NOT NULL
       PRO_DESCRIPTION 'The department number of the employee'
       PRO_COL_LABEL 'Dept. No.'
)PRO_HIDDEN 'Y' PRO_DESCRIPTION 'All Employees';

A hidden table is created and it has a specified description.

CREATE TABLE statement to create a multi-tenant table that overrides areas of the selected tenant table partition

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 ;

CREATE TABLE statement to create a multi-tenant table that uses the default area for all the table partition of a tenant

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;

CREATE TABLE statement to create a multi-tenant table that uses the default area for all the area tenants except the 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";

CREATE TABLE statement to create partitioned tables

The following example illustrates partitioning 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 USING TABLE AREA "area_pt"
(
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"
);

CREATE TABLE statement to partition a table by LIST

The following example illustrates partitioning a table by LIST.

CREATE TABLE Pub.tpcustomer
(
custid int,
custname VARCHAR (50),
city VARCHAR (10),
salary int
)
PARTITION BY LIST CITY
USING TABLE AREA "custtab_area"
USING INDEX AREA "custidx_area"
(
PARTITION p1 VALUES IN ( 'Atlanta' ),
PARTITION p2 VALUES IN ( 'Montgomery' ),
PARTITION p3 VALUES IN ( 'Boston' ));

In the above example, partitions p1, p2, and p3 contain customers whose cities are Atlanta, Montgomery, and Boston, respectively.

Creating constraints on a partitioned table

The following examples illustrate creating constraints on a partitioned table.

Note: If a constraint includes partition key column as the leading prefix of its constraint columns, then a local index is created for the constraint, otherwise, a global index is created. For more information about local and global indexes, see the section.

CREATE TABLE Pub.tpcustomer
(
"cust_num" int,
"SalesRep" VARCHAR (160),
"tp_date" date primary key,
"r_value" int
)
PARTITION BY RANGE "tp_date"
USING TABLE AREA "Tenant 1_table_area"
PARTITION "custtab_area"
USING INDEX AREA "custidx_area"
(
PARTITION tpcustomer_p1 VALUES <= ( '12/31/2011' ),
PARTITION tpcustomer_p2 VALUES <= ( '12/31/2012' ),
PARTITION tpcustomer_p3 VALUES <= ( '12/31/2013' )
);

A local index is created for the constraints in the above example.

CREATE TABLE Pub.tpcustomer
(
"cust_num" int,
Name VARCHAR (60) UNIQUE,
Address VARCHAR (160),
Zip VARCHAR (160),
"SalesRep" VARCHAR (160),
"tp_date" date,
"r_value" int
)
PARTITION BY RANGE "tp_date"
USING TABLE AREA "Tenant 1_table_area"
(
PARTITION tpcustomer_p1 VALUES <= ( '12/31/2011' ),
PARTITION tpcustomer_p2 VALUES <= ( '12/31/2012' ),
PARTITION tpcustomer_p3 VALUES <= ( '12/31/2013' )
);

A global index is created for the constraints in the above 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','New York'),
USING INDEX AREA "Secunderabad index area"
PARTITION USA_MA_BD VALUES IN (‘USA,'MA','Bedford'))

Creating a table with LOB partitions

The following example shows 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 Tab1_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.

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

Creating a table with read-only partitions

The following example illustrates creating a table with read-only partitions:

CREATE TABLE Pub.tpcustomer
(
a int,
b int,
c int,
d int
)
PARTITION BY LIST a
SUBPARTITION BY LIST b
SUBPARTITION BY LIST c
USING TABLE AREA "Tenant 1_table_area"
(
PARTITION sub1_a VALUES IN ( 10, 11, 12) READ_ONLY,
PARTITION sub1_b VALUES IN ( 20, 21, 22),
PARTITION sub1_c VALUES IN ( 30, 31, 32) READ_ONLY
);

Creating a table without any partition schema definition

CREATE PARTITION TABLE order
(
orderid integer,
Item varchar (50),
Order_date date,
Country varchar (50),
State varchar (50),
City varchar (50))
AREA "area_type2";

In this section:

Authorization

Related Statement