Progress
SQL-89
Guide and Reference


CREATE TABLE Statement

Creates a new table containing the columns you specify.

SYNTAX
CREATE TABLE table-name
  (  {   column data-type [ column-options ] 
       | UNIQUE ( column [ , column ] )
     }
     [    , column data-type [ column-options ]
       | , UNIQUE ( column [ , column ] )
     ] ...
  ) 

table-name

The name of the base table you want to create.

column

The name of a column for the table.

data-type

The data type of the column you want to define. The data types are CHARACTER, INTEGER, SMALLINT, DECIMAL, FLOAT, DATE, and LOGICAL.

column-options

Options that describe the field. This is the syntax for column-options:

SYNTAX
  [ COLUMN-LABEL string ]
  [ DEFAULT initial-value ]
  [ FORMAT string ]
  [ LABEL string ]
  [ [ NOT ] CASE-SENSITIVE ] 
  [ NOT NULL [ UNIQUE ] ] 

COLUMN-LABEL string

Specifies a new label for the column when its values are displayed vertically (in columns) on the screen or in a printed report. You must specify the label as a constant enclosed in single or double quotation marks. You can create stacked or multi-line column labels. Indicate a line break within the label by inserting an exclamation point.

DEFAULT initial-value

Assigns a default value for the column. This is the same as setting the initial value for a field in the Progress Data Dictionary.

FORMAT string

Specifies a new display format for the column. You must enclose the string in single or double quotation marks. The data type determines the storage format of the column.

LABEL string

Specifies a new label for the column. You must enclose the string in single or double quotation marks.

[ NOT ] CASE-SENSITIVE

Indicates whether the values in a character column, and comparisons made to them, must be case sensitive. The default is case sensitive if you used the ANSI SQL (-Q) parameter. Otherwise, the default is not case sensitive. (See the -Q startup parameter information in the “NOTES” section.)

NOT NULL [ UNIQUE ]

Indicates that the column must have a value. The UNIQUE option indicates that all values in the column must be unique.

UNIQUE (column-name [ , column-name ] ... )

Indicates that all values in the column or combination of columns must be unique. If you use the UNIQUE option, Progress/SQL automatically creates a unique index. The index is named sql-uniqn, where n is a number that makes the index name unique within the table. Each column-name you specify must be defined with the NOT NULL option within the CREATE TABLE statement.

EXAMPLE

This example creates a new base table with the specific columns for name, job, dept, etc. It creates a unique index on the combination of the name and startdate fields.

r-cretab.p
CREATE TABLE Employee (Emp_Num INTEGER NOT NULL UNIQUE,
                    Name CHARACTER(30) NOT NULL,
                    Job CHARACTER(15),
                    Dept CHARACTER(15),
                    Startdate DATE NOT NULL,
                    Salary DECIMAL(8,2),
                    UNIQUE(Name, Startdate)). 

NOTES

SEE ALSO

ALTER TABLE Statement, CREATE SCHEMA Statement (ESQL Only), DROP TABLE Statement, GRANT Statement, REVOKE Statement


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