Progress
SQL-89
Guide and Reference
ALTER TABLE Statement
Adds new columns to a table, deletes columns from a table, or changes the format or labels associated with an existing column.
table-name
Represents the name of the table you want to change.
ADD COLUMN column-name data-type
Adds a new column to the specified table. You must specify the data type of the new column you want to add. The Progress/SQL data types are CHARACTER, INTEGER, SMALLINT, DECIMAL, FLOAT, DATE, REAL, NUMERIC, and LOGICAL.
FORMAT string
Specifies the 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 label for the column. You must enclose the string in single or double quotation marks.
COLUMN-LABEL string
Specifies a label for the column when its values are displayed vertically (in columns) on the screen or in a printed report. You must enclose the string in single or double quotation marks.
DEFAULT initial-value
Assigns a default value for the column. This is the same as setting the default value for a field in the Progress Data Dictionary.
[
NOT
]CASE-SENSITIVE
Indicates whether the values in a character column and the comparisons made to it must be case sensitive. The default is case sensitive if you used the ANSI SQL (-Q) parameter. Otherwise, the default is not case sensitive. You cannot specify the [NOT] CASE-SENSITIVE option on ALTER COLUMN if the column being altered already participates in any indexes.
DROP COLUMN column-name
Deletes the specified column from the table.
ALTER COLUMN column-name
Changes the display format, label, default value, or column label for the specified column. (You can also change all four items for the specified column with ALTER COLUMN.)
EXAMPLESThe following examples show how you can add or change a column for the cust_table table.
NOTES
- Only the owner of the table can use the ALTER TABLE statement.
- When adding a new column to a table, you cannot use the NOT NULL and UNIQUE clauses unless you use the DEFAULT clause. If you do not use the DEFAULT clause, Progress/SQL inserts null values into all existing rows for the new column.
- When you add a new column through the ALTER TABLE statement, the update privilege is set to the owner of the table. You have to grant update privileges to other users explicitly.
- If you use the ANSI SQL (-Q) parameter to enforce strict ANSI SQL conformance, all columns added with the ALTER TABLE statement are case sensitive by default. To override the default, use the NOT CASE-SENSITIVE option for each specific column. See the Progress Startup Command and Parameter Reference for more information about the -Q startup parameter.
- 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.
SEE ALSO
CREATE SCHEMA Statement (ESQL Only), CREATE TABLE Statement, DROP TABLE Statement, GRANT Statement, REVOKE Statement
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |