Progress
SQL-89
Guide and Reference


Creating Tables

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

NOTE: You cannot create tables for non-Progress databases.

With the CREATE TABLE statement, you enter a name for the table and define one or more columns. No two columns can have the same name in the same table. For each column you name, you must enter a data type for that column. You can also enter column options for each column.

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

Use the optional keywords NOT NULL to indicate that the column must have a value and UNIQUE to indicate that all values in the column must be unique.

The optional keyword CASE-SENSITIVE ensures the column is case sensitive, in accordance with the ANSI SQL89 Level 2 standard. If you indicate that the column is case sensitive, any comparisons to the column are also case sensitive. For example, the comparison “IF CONTACT = NAME” is case sensitive if either the column CONTACT or NAME is case sensitive.

If you attempt to use the CASE-SENSITIVE keyword with noncharacter columns, Progress returns a warning error and the keyword is ignored. Use NOT CASE-SENSITIVE to disable the case sensitive default created using the ANSI SQL (-Q) startup parameter.

Including the UNIQUE keyword in an individual column definition is equivalent to including that column in a separate UNIQUE clause. When you use the keyword UNIQUE to specify that a column or combination of columns must be unique, Progress automatically creates an index on the column or columns and adds records to the _Index and _Index-Field schema tables. The index is named sql-uniqn, where n is a number Progress assigns.

Consider the following points when creating a unique index:

The following example creates a table named cust_table. The cust_num field is indexed because of the UNIQUE option, and its column definition is specified as NOT NULL.

CREATE TABLE Cust_Table
  (Cust_Num INTEGER NOT NULL,
   Name CHARACTER (30),
   Address CHARACTER (30),
   City CHARACTER (15),
   State CHARACTER (2),
   Phone CHARACTER (10),
   Credit_Limit DECIMAL (6),
   Balance DECIMAL (6),
   UNIQUE (Cust_Num) ).  

You can also describe how the data appears on the screen or in a printed report by using the Progress syntax for display formats and labels. For information on the FORMAT, LABEL, and COLUMN-LABEL format phrases, see the Format Phrase reference entry in the Progress Language Reference.


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