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:
ALTER TABLE table_name
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
ALTER TABLE publisher
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:
ALTER TABLE publisher
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:
ALTER TABLE table_name
ADD table_constraint
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:
ALTER TABLE customer
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
ALTER TABLE some_table
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:
ALTER TABLE table_name
ALTER COLUMN column_name
TYPE new_data_type
◊ To add or change a default value only (without changing the data type or size of the column), include the DEFAULT keyword:
ALTER TABLE table_name
ALTER column_name
SET DEFAULT new_default_value
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:
ALTER TABLE table_name
DROP COLUMN column_name;
◊ To delete a table constraint such as a primary or foreign key, use DROP CONSTRAINT:
ALTER TABLE table_name
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.
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
RENAME TO new_table_name
◊ To rename a column, include both the old and new column names separated by the keyword TO:
ALTER TABLE table_name
RENAME current_column_name
TO new_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 remove a domain's default value, use
ALTER DOMAIN domain_name
DROP DEFAULT
◊ To change a domain's NULL or NOT NULL status, use
ALTER DOMAIN domain_name
SET NOT NULL
or
ALTER DOMAIN domain_name
DROP NOT NULL
◊ To add a new constraint to the domain, use
ALTER DOMAIN domain_name
ADD constraint_name
domain_constraint_expression
◊ To remove a constraint from the domain, use
ALTER DOMAIN domain_name
DROP constraint_name
Deleting Database Elements
To delete a structural element from a database, you drop the element. For example, to delete a table, you would type
DROP TABLE table_name
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.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.