11. Keeping the Design Up to Date
One of the benefits that relational DBMSs have over DBMSs based on older data models is that the schema is easy to change. As long as a table isnapos;t being used at the time you want to modify it, its design can be changed without affecting other tables in the database. (This is said with the caveat that the presence of foreign key constraints may prevent some deletions and modifications or cause other modifications to occur.) The SQL statements that modify database structures are therefore an important part of a database administrator's arsenal. In this chapter, weapos;ll look at the types of changes that can be made and how to make them.
Modifying Tables
With the exception of tables, structural database elements are largely unchangeable. When you want to modify them, you must delete them from the database and create them from scratch. In contrast, just about every characteristic of a table can be modified without deleting the table using the ALTER TABLE statement.
Note: DBMS support for the parts of ALTER TABLE varies considerably. It is not unusual to find that all you can do is add a column or increase the size of a character column, for example. As always, you will need to consult the documentation for your particular DBMS to see exactly what is available.
Adding New Columns
To add a new column to a table, you use the ALTER TABLE statement with the following syntax:
ADD column_name column_data_type column_constraints
For example, if someone at the rare book store wanted to add a telephone number to the
publisher table, he or she would use
ADD publisher_phone CHAR (11);
To add more than one column at the same time, simply separate the clauses for the new columns with commas:
ADD publisher_phone CHAR (11),
ADD publisher_street CHAR (30),
ADD publisher_city CHAR (30),
ADD publisher_state_prov CHAR (2),
ADD publisher_zip_postcode CHAR (12),
ADD publisher_country CHAR (10);
There is one caveat that goes along with adding columns: If you have any application programs that use the SELECT * syntax, then any new columns that you add to a table will be included in the output of that query. The result may be either the disclosure of data you wanted to keep secret or application programs that no longer work properly. Because SQL allows you to add columns to tables without restriction, you should avoid using the SELECT * syntax in application programs.
Adding Table Constraints
You can add table constraints such as foreign keys at any time. To do so, include the new constraint in the ADD clause of an ALTER TABLE statement:
Assume, for example, that someone at the rare book store created a new table named
regions and included all the two-character U.S. state and Canadian province abbreviations. The table would then need to add a reference from the
customer table:
ADD FOREIGN KEY customer2regions (state_ province)
REFERENCES regions (region_name);
When you add a foreign key constraint to a table, the DBMS verifies that all existing data in the table meet that constraint. If they do not, the ALTER TABLE will fail.
If you have created a table without a primary key, you can add one with
ADD PRIMARY KEY (key_columns);
Modifying Columns
You modify columns by changing any characteristic of the column, including its type, size, and constraints:
◊ To replace a complete column definition, use an ALTER clause with the current column and the new column characteristics:
◊ To add or change a default value only (without changing the data type or size of the column), include the DEFAULT keyword:
SET DEFAULT new_default_value
◊
To switch between allowing nulls and not allowing nulls without changing any other column characteristics, add SET or DROP NOT NULL as appropriate:
ALTER column_name SET NOT NULL
or
MODIFY column_name DROP NOT NULL
When you change the data type of a column, the DBMS will attempt to convert any existing values to the new data type. If the current values cannot be converted, then the table modification will not be performed. In general, most columns can be converted to characters. However, conversions from a character data type to numbers or datetimes require that existing data represent legal values in the new data type.
Deleting Table Elements
You can delete parts of a table as needed:
◊ To delete a column, use a DROP clause in an ALTER TABLE statement, followed by the name of the column to be deleted:
◊ To delete a table constraint such as a primary or foreign key, use DROP CONSTRAINT:
DROP CONSTRAINT constraint_name;
Although you can delete a table's primary key, keep in mind that if you do not add a new one, you may not be able to modify the contents of the table.
◊
To remove a default value from a column use:
DROP column_name DEFAULT;
Renaming Table Elements
You can rename both tables and columns:
◊ To rename a table, place the new table name after the RENAME keyword:
ALTER TABLE current_table_name
◊ To rename a column, include both the old and new column names separated by the keyword TO:
RENAME current_column_name
Modifying Domains
If you have created custom domains, those domains can be modified as needed. Keep in mind, however, that if the data currently in the column donapos;t meet the criteria of the modified domain, the modification may not be allowed. (Such behavior is implementation dependent)
Domain modifications use the ALTER statement, much like modifying tables:
◊
To change a domain's default value, use
SET DEFAULT default_value
◊ To remove a domain's default value, use
◊ To change a domain's NULL or NOT NULL status, use
◊ To add a new constraint to the domain, use
domain_constraint_expression
◊ To remove a constraint from the domain, use
Deleting Database Elements
To delete a structural element from a database, you
drop the element. For example, to delete a table, you would type
Dropping a table is irreversible. In most cases, the DBMS will not bother to ask “Are you sure?” but will immediately delete the structure of the table and all of its data.
You can remove the following structural elements from a data-base with the DROP statement:
A DROP of a table or view will fail if the element being dropped is currently in use by another user.
The action of a DBMS when you attempt to DROP a table depends to some extent on whether the table contains primary keys with foreign key references and what action was specified when the table was created. If the action is RESTRICT, then the DROP will fail. In contrast, for example, if the action is CASCADE, related foreign key rows will be deleted from their table(s) when the primary key table is dropped.