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 increase the file size allocated for the databank.
Change which file the HOTELDB is stored in from its previous file to file 'SQLDB:HOTELDB.DBF':
Note: The file specification is in Alpha/Open VMS format.ALTER DATABANK HOTELDB INTO 'SQLDB:HOTELDB.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 ... INTO to change the file specification in the data dictionary.
Change the option on the HOTELDB databank from TRANS to LOG:ALTER DATABANK HOTELDB TO LOG OPTION;
Increase the size of the HOTELDB database by 20 Mimer pages:ALTER DATABANK HOTELDB ADD 20 PAGES;
Note: Use of the ALTER DATABANK ... ADD statement is not strictly necessary. 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 change a column in an existing table to have a specified data type or to belong to a specified domain, provided the old and new data types are assignment-compatible, see the Mimer SQL Reference Manual and the column is not be referenced by any constraints or views
- to drop the default value for a column in an existing table
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 indicator.
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.
Add a column called NOSMOKE with a data type of CHAR(1) to the BOOK_GUEST table.ALTER TABLE BOOK_GUEST ADD NOSMOKE CHAR(1);
This creates a column containing the NULL indicator for each row in the table. If an constraint is added to a table it is checked that the data in the table fulfil the restriction in the constraint.
Drop the column TELEPHONE from the table BOOK_GUEST, subject to the condition that there are no other objects dependent on this column:ALTER TABLE BOOK_GUEST DROP TELEPHONE RESTRICT;
Change the length of the column ADDRESS in the table BOOK_GUEST:ALTER TABLE BOOK_GUEST ALTER COLUMN ADDRESS VARCHAR(100);
Drop the column TELEPHONE from the table BOOK_GUEST, if dependent objects exist, these are dropped as well:ALTER TABLE BOOK_GUEST DROP TELEPHONE CASCADE;
Change the default value for the column BOOKING_DATE, the new default value is current date:ALTER TABLE BOOK_GUEST ALTER BOOKING_DATE SET DEFAULT CURRENT_DATE;
Drop the check constraint CITY_CHECK from the HOTEL table:ALTER TABLE HOTEL DROP CONSTRAINT CITY_CHECK;
Redefine a foreign key constraint for the BOOK_GUEST table:ALTER TABLE BOOK_GUEST DROP CONSTRAINT SQL_FOREIGN_KEY_4375; ALTER TABLE BOOK_GUEST ADD CONSTRAINT ROOMS_FOREIGN FOREIGN KEY(ROOMNO) REFERENCES ROOMS ON DELETE CASCADE;
Drop the default value for the column BOOKING_DATE:ALTER TABLE BOOK_GUEST ALTER BOOKING_DATE 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, depending 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 user SAMMY's password to 'SamJo'.ALTER IDENT SAMMY USING 'SamJo';
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.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40