Use the ALTER TABLE command to modify an existing table.
Options
,
Indicates that this part of the command is repeatable.
ADD
Specifies the column and its definition, or a table constraint to add.
ADD CASCADE
Maintains primary/foreign key relationships automatically. For example, if you either UPDATE or DELETE a primary key value from a table, the corresponding foreign key values are updated or deleted automatically. A CASCADE can applied to UPDATE, DELETE or BOTH to specific primary keys. By not specifying either UPDATE or DELETE, both CASCADE restrictions will be enforced upon the primary/foreign key tables. Separate UPDATE and DELETE data restrictions can allow a CASCADE to be enforced for records that are updated, but not enforced when records are deleted, in order to avoid an accidental or undesired record delete. CASCADE can only be added to tables with primary keys.
ADD TRIGGER
Adds the specified triggers to the table. Triggers run a stored procedure when an UPDATE, DELETE, or INSERT command is executed. If you are using BEFORE and/or AFTER triggers, BOTH must be ADDed at the same time.
AFTER
Sets the trigger to activate after the INSERT, UPDATE or DELETE action.
ALTER
Modifies a column definition.
BEFORE
Sets the trigger to activate before the INSERT, UPDATE or DELETE action. This is the default setting if the BEFORE/AFTER parameter is unused.
CASE
Specifies that the data values will be case sensitive.
CHECK (condition)
Sets a condition to be satisfied before an update or insertion of a row can occur, which creates an R:BASE rule.
(collist)
Specifies a list of one or more column names, separated by a comma (or the current delimiter), used in the unique key specification. This option is only used when referencing a unique key.
colname
Specifies a 1 to 128 character column name.
COLUMN
Specifies the column to add, drop, or alter.
conname
Specifies a constraint name.
datatype
Specifies an R:BASE data type.
DEFAULT
Specifies a default value for the column if no value is provided by the user.
DROP
Removes a column or a constraint. A column, including both its structure and data, is removed from the table. Dropping a constraint removes a primary key, foreign key, unique key, or a not-null constraint.
DROP CASCADE
Disables the CASCADE feature so that primary/foreign key relationships are not maintained automatically.
DROP CONSTRAINT
Removes a constraint.
DROP DEFAULT
Removes a column's default value.
DROP TRIGGER
Drops either all triggers for a table, or the specified INSERT, UPDATE, or DELETE trigger. There is no need to specify BEFORE and/or AFTER because both triggers will be deleted if they are present using this command.
= (expression)
Determines a value using a text or arithmetic formula. The expression can include other columns from the table, constant values, functions, or system variables such as #date, #time, and #pi.
(<FKMSG>)
Creates a constraint violation message to appear whenever a foreign-key data integrity violation occurs. The message can suit the meaning of your data, such as "You must enter a valid number" for a foreign-key constraint violation. You can define two messages: one for inserting and one for updating. A constraint must be dropped, then recreated in order to modify the violation message.
FOREIGN INDEX
With the FASTFK setting on, creates a foreign key that has an index using row pointers for data retrieval on selected columns.
FOREIGN KEY
Specifies a column or set of columns required to match values in a particular primary key or unique key constraint defined in a table.
FOREIGN KEY (collist)
If (collist) comprises one column, this option is equivalent to FOREIGN KEY. If two or more columns are included in (collist), the values in the listed columns must be unique as a group in each row. Each column must be separated by a comma (or the current delimiter).
(<NNMSG>)
Creates a constraint violation message to appear whenever a not-null data integrity violation occurs. The message can suit the meaning of your data, such as "You must enter a valid number" for a not-null constraint violation. A constraint must be dropped, then recreated in order to modify the violation message.
NOCHECK
Optional NOCHECK parameter does not update references to views, tables, and columns in forms, reports, labels, access rights, and rules. In this case, user assumes the responsibilities to update any references to views, tables, and columns in forms, reports, labels, access rights, and rules. This condition is ONLY available for the ALTER COLUMN parameter.
NOT NULL
Prevents a column from accepting null values, but permits it to accept duplicate values. If this option is specified without a setting for a default value, you cannot insert rows without specifying values for the given column.
(<PKMSG>)
Creates a constraint violation message to appear whenever a primary-key data integrity violation occurs. The message can suit the meaning of your data, such as "You must enter a valid number" for a primary-key constraint violation. You can define three messages: one for uniqueness, one for deleting, and one for updating. A constraint must be dropped, then recreated in order to modify the violation message.
PRIMARY KEY
Specifies the column(s) to designate as a primary key constraint.
PRIMARY KEY (collist)
If (collist) comprises one column, this option is equivalent to PRIMARY KEY. If two or more columns are included in (collist), the values in the listed columns must be unique as a group in each row. Only columns defined as not null can be included in (collist). Each column must be separated by a comma (or the current delimiter).
procname
The procedure name. If a procedure by this name already exists in the database, an error is generated.
REFERENCES
Identifies the primary key or unique key table to which the foreign key refers.
SET DEFAULT
Changes a column's default value.
(size)
Defines the length of a column of either the CHARACTER data type (if not the default 1) or TEXT data type (if not the default 8). Defines the precision and scale of a column of the DECIMAL or NUMERIC data type, if not the default of precision 9 and scale 0 (9,0). VARBIT, VARCHAR, and BIT either require or can have a size.
tblname
Specifies a 1 to 18 character table name.
(<UMSG>)
Creates a constraint violation message to appear whenever a unique-key data integrity violation occurs. The message can suit the meaning of your data, such as "You must enter a valid number" for a unique-key constraint violation. You can define three messages: one for uniqueness, one for deleting, and one for updating. A constraint must be dropped, then recreated in order to modify the violation message.
UNIQUE
Requires the values in a column to be unique by creating a unique key constraint.
UNIQUE (collist)
If (collist) is one column, this option is equivalent to UNIQUE. If two or more columns are included in (collist), the values in the listed columns must be unique as a group in each row. Only columns defined as not null can be included in (collist). Each column must be separated by a comma (or the current delimiter).
USER
NULL
(value)
Default USER: Specifies the default value to be the user identifier.
Default NULL: Specifies the default value to be null.
Default (value): Specifies the default to be the indicated value.
About the ALTER TABLE Command
ALTER TABLE creates a temporary internal table by copying a table's structure and data. You must have enough disk space to hold another copy of a table, and your database should not exceed the number of tables and columns R:BASE allows, which includes user-defined tables and system tables.
After the ALTER TABLE command has been executed, the temporary table goes away; however, the disk space the temporary table occupied is not available. To recover this space, pack or reload the database using the PACK or RELOAD commands.
The ALTER TABLE command is supported in EEP's for versions 6.5++ and higher.
Adding Columns
When you add a new column to a database, specify the name, data type, and length when the data type for the column is CHARACTER or TEXT, or precision and scale when the data type for the column is DECIMAL or NUMERIC. When the column is computed, specify the name and expression-a data type is optional. When the column already exists in the database, specify only the name-R:BASE uses the existing data type, and length, if applicable.
Database Access Rights with ALTER TABLE
When access rights for a table or view have been assigned using the GRANT command, ALTER TABLE requires the database owner's user-identifier or permission from the owner to alter specific tables. For more information about
assigning access rights, see the "GRANT" entry.
Limitations of the ALTER TABLE Command
You cannot assign an index to a new column or transfer the index of an existing column with ALTER TABLE. If the added column should be indexed, use the CREATE INDEX command.
You also cannot add or transfer rules with ALTER TABLE. If you want a rule to apply to a column in the table, you must add it with the RULES command or use the Database Designer.
You cannot use ALTER TABLE to modify a view.
Examples
The following command adds mailadrs, a TEXT column 40 characters wide, at the end (or far right) of the customer table.
ALTER TABLE customer ADD mailadrs TEXT 40
The following command adds the profit column at the end of product table. The value of profit is computed from the current row values for listprice multiplied by 1.05. The data type specified is REAL.
ALTER TABLE product ADD profit=(listprice * 1.05) REAL
The following command defines columns one through three as a case-sensitive primary key. Before you use this command, you must add a not-null constraint to each of the columns.
ALTER TABLE tablename ADD PRIMARY KEY CASE (column1, column2, column3) ('This is a message from the primary key')
The following command line adds a foreign index to the custidcolumn and references the primary key in the customer table.
ALTER TABLE transmaster ADD FOREIGN INDEX (custid) REFERENCES customer
The following command line adds the test trigger for SampleTriggers Table.
DROP PROCEDURE MySampleTrigger
PUT AFTER.PRC AS MySampleTrigger
ALTER TABLE SampleTriggers ADD TRIGGER INSERT AFTER MySampleTrigger
RETURN
