Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## 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 first syntax form explicitly specifies column definitions. The second syntax form, with the `AS` `query_expression` clause, implicitly defines the columns using the columns in a query expression.

> #### Syntax

>     CREATE TABLE [ owner_name.]table_name
>       ( { column_definition | table_constraint }, ...)
>         [ AREA area_name ]
>         [ progress_table_attribute_keyword value ]
>         ;
>     CREATE TABLE [ owner_name.]table_name
>         [ (column_name [ NOT NULL] , ... ) ]
>         [ AREA area_name ]
>         AS query_expression
>         ;

> ``owner_name``

> Specifies the owner of the table. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

> ``table_name``

> Names the table you are defining.

> ``column_definition``

> Syntax
>     column_name   data_type
>       [ COLLATE case_insensitive | case_sensitive ]
>       [ DEFAULT { literal | NULL | SYSDATE | SYSTIME | SYSTIME STAMP } ]
>       [ column_constraint [ column_constraint , ... ] ]
>       [ progress_column_attribute_keyword value
>       [progress_column_attribute_keyword value ] ...]

> ``column_name data_type``

> Names a column and associates a data type with it. The column names specified must be different from other column names in the table definition. The data_type must be supported by OpenEdge.

> When a table contains more than one column, a comma separator is required after each column_definition except for the final column_definition.

> `COLLATE`

> Indicates the column’s case sensitivity. Note the default is `case_sensitive``.`

> ``case_insensitive``

> Indicates the column will be case insensitive. The value for `case_insensitive` can be any name ending in `I` (for example: `BASIC_I`).

> ``case_sensitive``

> Indicates the column will be case sensitive. The value for `case_sensitive` can be any name ending in `S` (for example: `BASIC_S`). Note that `case_sensitive` is the default value for `COLLATE`.

> `DEFAULT`

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

> The `DEFAULT` clause accepts the following arguments:

>     literal
> | An integer, numeric, or string constant.
>     NULL
> | A null value.
>     SYSDATE
> | The current date. Valid only for columns defined with `DATE` data types. `SYSDATE` is equivalent to the Progress default keyword `TODAY`.
>     SYSTIME
> | The current time. A `TIME` value.
>     SYSTIMESTAMP
> | The current date and time. A `TIMESTAMP` value.

> ``column_constraint``

> Specifies a constraint that will be applied while inserting or updating a value in the associated column.

> ``progress_column_attribute_keyword value``

> Progress 4GL column attribute keyword and value. See the "Syntax for 4GL Attributes" section for a list of column attribute keywords.

> ``table_constraint``

> Specifies a constraint that will be applied while inserting or updating a row in the table.

> `AREA area_name`

> Specifies the name of the storage area where data stored in the table is to be stored.

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

> ``progress_table_attribute_keyword value``

> Progress 4GL table attribute keyword and value. See the "Syntax for 4GL Attributes" section for a list of table attribute keywords.

> `AS query_expression`

> Specifies a query expression to use for the data types and data values of the table’s columns. The types and lengths of the columns of the query expression result become the types and lengths of the respective columns in the table created. The rows in the resultant set of the query expression are inserted into the table after creating the table. In this form of the `CREATE TABLE` statement, column names are optional. If omitted, the names of the table’s columns are taken from the column names of the query expression.

> Examples

> In the following `CREATE TABLE` supplier_item example, the user issuing the `CREATE TABLE` statement must have `REFERENCES` privilege on the `itemno` column of the table `john.item`:

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

> The table will be created in the current owner schema.

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

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

> The following example shows the `AS` `query_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') ;

> The following example includes a `NOT NULL` column constraint and `DEFAULT` clauses for column definitions:

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

> The following example shows how to create a table with two columns, both of which have 4GL descriptions and column labels specified:

>     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’;

> The table itself has a description specified, and will be created as hidden.

> ##### Authorization

> Must have `DBA` privilege, `RESOURCE` privilege or `SELECT` privilege.

> ##### Related statements

> DROP TABLE

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095