SQL Bible, Second Edition

Chapter 5. Altering and Destroying RDBMS Objects

Objects created within a database are not cast in stone. The ability to easily change database objects gives an RDBMS a big advantage over nonrelational models because flexibility is built into the relational database concept. This includes the ability to create, modify, and destroy database objects. Needless to say, all our three major vendors provide this capability, usually with vendor extensions that enable you to modify one entity without affecting other entities.

Note

Examples in this chapter assume incremental buildup of the ACME database started in Chapter 4, "Creating RDBMS Objects." Constraints implemented in the database scripts that you can download from the book's accompanying website may prevent some of the statements from successful execution.

Tables

You already know that the table is the central RDBMS object. In a perfect world, you would never have to change anything in a table's structure after it was created. In real life, however, you need to modify table definitions quite often. Changing business rules are usually the main reason; incorrect initial design is also not uncommon, especially in test environments. The whole idea behind the ALTER TABLE statement is to make the table definition changes as fast and as painless as possible. The DROP TABLE statement is used to remove a table (both its data and definition) from the database.

ALTER TABLE statement

This section explains how ALTER TABLE is implemented by different ...