Progress
SQL-89
Guide and Reference
CREATE TABLE Statement
Creates a new table containing the columns you specify.
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:
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.
EXAMPLEThis 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.
NOTES
- The user who creates a table can grant privileges on that table to other users. See the GRANT statement reference entry for more information.
- You cannot use the DROP INDEX statement to remove an index created with the UNIQUE option in a CREATE TABLE statement because the index is part of the base table definition. To create an index that you can later remove, use CREATE INDEX.
- You cannot use CREATE TABLE to create a table in an ORACLE database. However, you can use it to create a table in a schema holder database.
- Use the CASE-SENSITIVE option only to distinguish between uppercase and lowercase values entered for a character column. For example, use CASE-SENSITIVE to define a column for a part number that contains mixed uppercase and lowercase characters.
- If you use the ANSI SQL (-Q) parameter to enforce strict ANSI SQL conformance, all columns that the CREATE TABLE statement creates are case sensitive by default. Use the NOT CASE-SENSITIVE option for each column that you want to override the default. See the Progress Startup Command and Parameter Reference for more information about the -Q startup parameter.
- When you use the -Q parameter to start a Progress session, case sensitivity is not enforced for columns that are already defined as case insensitive.
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 |