Progress
SQL-89
Guide and Reference
Creating Tables
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.
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 first unique column you specify in the CREATE TABLE statement becomes the primary index. If you do not specify a unique constraint on any column, Progress creates a default index named sql-default, which orders rows by their internal RECID.
- If you do not specify any unique columns in the CREATE TABLE statement, you can create indexes later on.
- You cannot use the DROP INDEX statement to remove an index created with the UNIQUE option in a CREATE TABLE statement. This is because such an index is part of the basic table definition and its deletion removes the unique constraint check.
- If you include a column in a separate UNIQUE clause, you must also specify that the column is NOT NULL in the individual column definition.
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.
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 |