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 be 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 is executed. If you are using BEFORE and AFTER triggers, BOTH must be ADDed at the same time.
AFTER
Specifies the AFTER trigger event to activate or drop the INSERT, UPDATE or DELETE action.
ALTER
Modifies a column definition.
BEFORE
Specifies the BEFORE trigger event to activate or drop the INSERT, UPDATE or DELETE action. This is the default setting when creating a trigger, 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 column name. The column name is limited to 128 characters.
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. The specified default value must match the column's data type (e.g. "PA" for a column that stores state address abbreviations). With TEXT columns, do not enclose the default value in the QUOTES character. The system variables #DATE, #TIME, and #NOW may be specified as default values. Static and global variables may also be specified as default values. When using system variables, static variables, or global variables, the value must be a dotted variable (the period must be included).
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 triggers from a table. If the INSERT, UPDATE, or DELETE actions is not specified, all triggers are dropped from the table. If the BEFORE or AFTER events are not specified for an INSERT, UPDATE, or DELETE action, both BEFORE and AFTER triggers for the specified action are dropped from the table.
DUPLICATE tblname.colname
Copies the data type, length, default value, and NOT NULL flag if any. DUPLICATE does not support computed columns, and will not duplicate any comment/description for the column.
= (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 command.
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 the 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 table name. The table name is limited to 128 characters.
(<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 EEPs.
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 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.
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.
You cannot add a foreign key to a temporary table.
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 "computed" 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 adds an "update only" cascade to the employee table.
ALTER TABLE employee ADD CASCADE UPDATE
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 custid column and references the primary key in the customer table.
ALTER TABLE transmaster ADD FOREIGN INDEX (custid) REFERENCES customer
The following command drops the before insert trigger in the InvoiceHeader table.
ALTER TABLE InvoiceHeader DROP TRIGGER INSERT BEFORE
The following command line adds the test trigger for the SampleTriggers Table.
DROP PROCEDURE MySampleTrigger
PUT AFTER.PRC AS MySampleTrigger
ALTER TABLE SampleTriggers ADD TRIGGER INSERT AFTER MySampleTrigger
RETURN
