Altering Databanks, Tables and Idents
The following sections explain how to alter databanks, tables and idents. you can also read about which objects you cannot alter.
Altering a Databank
Databanks can only be altered by their creator.
There are three uses for the ALTER statement:
- to change the physical file location for a databank
- to change the transaction and logging options on the databank
- to manage the file size allocated for the databank.
Change which file the MIMER_ORDERS databank is stored in from its previous file to file 'DISK2:MIMER_ORDERS.DBF':
Note: The file specification is in Alpha/OpenVMS format.ALTER DATABANK mimer_orders SET FILE 'DISK2:MIMER_ORDERS.DBF';
Note: This statement changes the file name stored for the databank in the data dictionary. It does not actually move the databank to the new location.
To move a databank, begin by copying or renaming the file in the operating system and then use ALTER DATABANK... SET FILE to change the file specification in the data dictionary.
Change the option on the MIMER_BLOBS databank from TRANSACTION to LOG:ALTER DATABANK mimer_blobs SET OPTION LOG;
Set the size of the MIMER_BLOBS database to 2000 MB:ALTER DATABANK mimer_blobs SET FILESIZE 2000 M;
Note: Use of the ALTER DATABANK... SET FILESIZE statement is not strictly necessary because databank files are extended dynamically. However, increasing the file allocation by a relatively large figure can help to minimize file fragmentation and improve response times.
The ALTER TABLE statement changes the definition of the specified table and may only be used by the creator of the schema to which the table belongs.
There are the following uses for the ALTER TABLE statement:
- to add a new column or table constraint definition to an existing table
- to drop a column or table constraint from an existing table
- to change the default value for a column in an existing table
- to drop the default value for a column in a table
- to change a column in an existing table to have a specified data type, provided the old and new data types are assignment-compatible, see the Mimer SQL Reference Manual and the column is not referenced by any constraints or views
A new column created with the ALTER TABLE... ADD statement is appended to end of the existing column list. The new column will include the default value defined for the column or defined for the domain to which it belongs or, if no default value exists, the NULL value.
Note: If a column added to a table is defined as NOT NULL, then it must have a default value defined or belong to a domain which has a default value, because the NOT NULL column cannot be given NULL values.
Add a column called CREDIT_RATING with a data type of CHAR(1) to the CUSTOMERS table:ALTER TABLE customers ADD credit_rating CHAR(1);
This creates a column containing the NULL value in each row in the table.
If a constraint is added to a table, the data in the table is checked to ensure it fulfills the restriction in the constraint.
Drop the column DATE_OF_BIRTH from the table CUSTOMERS, subject to the condition that there are no other objects dependent on this column:ALTER TABLE customers DROP date_of_birth RESTRICT;
Drop the column DATE_OF_BIRTH from the table CUSTOMERS, if dependent objects exist, these are dropped as well:ALTER TABLE customers DROP date_of_birth CASCADE;
Change the length of the column FORMAT in the table FORMATS:ALTER TABLE formats ALTER COLUMN format VARCHAR(32);
Change the default value for the column REORDER_LEVEL, the new default value is one:ALTER TABLE items ALTER reorder_level SET DEFAULT 1;
Drop the check constraint ITM_PRICE_ILLEGAL from the ITEMS table:ALTER TABLE items DROP CONSTRAINT itm_price_illegal;
Redefine a foreign key constraint for the CUSTOMERS table:ALTER TABLE customers DROP CONSTRAINT cst_countries; ALTER TABLE customers ADD CONSTRAINT cst_countries FOREIGN KEY (country_code) REFERENCES countries ON DELETE CASCADE ON UPDATE NO ACTION;
Drop the default value for the column REGISTERED:ALTER TABLE customers ALTER registered DROP DEFAULT;
Note on Dropping
When dropping a column from a table, the CASCADE and RESTRICT keywords can be used to specify the action that will be taken on objects that are dependent on the dropped column.
If CASCADE is specified, dependent objects are also dropped. For instance if a dropped column is part of a primary key, the primary key will also be dropped.
If RESTRICT (the default) is specified and there are other objects affected, the statement will be aborted, with an error condition. See also, Dropping Objects from the Database.
Only passwords can be altered with the ALTER IDENT statement.
Ident names cannot be altered.
USER and PROGRAM idents can change their own password if they so wish.
Passwords can also be changed by the creator of the ident.
Change the ident MIMER_ADM's password to 'evjkl9u'.ALTER IDENT mimer_adm USING 'evjkl9u';
Objects Which May Not Be Altered
Domains, functions, procedures, modules, triggers, views and indexes cannot be altered. It is therefore important that you think through your domains and views thoroughly and carefully before you create them to make sure that they suit the needs of your database.
The functions and procedures contained in a module are created when the module is created and thereafter no alterations can be made to the module (the module and all the routines contained in it can, of course, be dropped).
The next section will discuss dropping objects and the results of this on the database.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40