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 though 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

We are now going to explain how ALTER TABLE is implemented by different vendors.

SQL99

The SQL99 ALTER TABLE syntax is:

ALTER TABLE <table_name> {[ADD [COLUMN] <column_definition>] |  [ALTER [COLUMN] <column_name> {SET DEFAULT <default> | DROP DEFAULT}] |  [DROP [COLUMN] <column_name> RESTRICT | CASCADE] |  [ADD <table_constraint>] |  [DROP CONSTRAINT <constraint_name> RESTRICT | CASCADE] };

Basically this code defines the syntax for adding or deleting a table column or a default value for a column as well as for dropping a table constraint.

Oracle 9i

The ALTER TABLE statement has even more options in Oracle than CREATE TABLE does. The following syntax lists only the ones most important in the context of this book:

ALTER TABLE [<qualifier>.]<table_name> {[<change_physical_attributes>] |  [ADD <column_name> <datatype> [<size1>[,<size2>]]      [DEFAULT <default_value>] [<column_constraint>,...]  ]..., |  [MODIFY <column_name> <datatype> [<size1>[,<size2>]] |      [DEFAULT <default_value>] [NULL | NOT NULL]  ]..., |  [DROP {COLUMN <column_name> | (<column_name>,...)}      [CASCADE CONSTRAINTS]  ] |  [MODIFY CONSTRAINT <constraint_name> <constraint_state>] |  [ADD {PRIMARY KEY (<column_name>,...) |         UNIQUE (<column_name>,...) |        CONSTRAINT <constraint_name> <constraint_definition>       }  ] |   [DROP {PRIMARY KEY |          UNIQUE (<column_name>,...) |         CONSTRAINT <contraint_name> [CASCADE]        }  ] |  [DISABLE | ENABLE {PRIMARY KEY |                      UNIQUE (<column_name>,...) |                     CONSTRAINT <constraint name> |                      ALL TRIGGERS                     }  ] |  [RENAME TO <new_table_name>] }

Changing physical attributes

Many different physical attributes of a table can be changed in Oracle using the ALTER TABLE statement. For example, it would be very easy to fix once you realized that your table was created in a wrong tablespace. The following example assumes the existence of tablespace DATA01:

ALTER TABLE SALESMAN MOVE TABLESPACE DATA01;

Note 

In our sample ACME database, all objects were created without explicitly specifying a tablespace. Also, user ACME was created without specifying a default tablespace. That means all tables and indexes are created in SYSTEM tablespace by default, which is absolutely unacceptable in a production database. You now know how to correct it using the ALTER TABLE statement.

See the Oracle documentation for more information on changing table physical attributes.

Adding columns

Often you will need to add a column to an existing table. For example, our ACME database can easily handle situations when a customer has more than one phone number. But now imagine we have a new set of business rules saying that if a customer or a salesman has more than one phone, we need a way to know which number is primary and what kind of phone it is (business, mobile, home, etc.). So, we want to add two new columns to PHONE: PHONE_PRIMARY_S with a default value of Y and PHONE_CATEGORY_S. We also want to specify the range of valid values for PHONE_PRIMARY_S (Y and N). The task is quite simple to implement:

ALTER TABLE PHONE ADD PHONE_PRIMARY_S CHAR(1) DEFAULT 'Y'      CHECK (PHONE_PRIMARY_S IN ('Y', 'N')) ADD PHONE_CATEGORY_S CHAR(15); 

As you can see from this example, Oracle allows you to use one ALTER TABLE statement to add multiple columns at the same time. You can drop multiple columns within one ALTER TABLE statement as well.

Tip 

If you add a column with a default value to an existing table, each row in the new column is automatically updated with the value specified for DEFAULT.

Modifying existing columns

You can modify columns in a couple of different ways. You can change the column's data type; increase or decrease the size of a CHARACTER or a RAW column (or precision of a NUMERIC column). You can also specify a new default for an existing column and/or add a NOT NULL constraint to it.

As usual, certain restrictions apply. You can decrease the size or precision of a column only if it's empty, that is, if it contains all NULLs. (You can decrease the size and precision with no limitations though.) You also can't add NOT NULL constraint to a column that contains NULLs. (Again, you can do it the other way around.) You can change a column's data type to a compatible data type only (for example, CHAR can be changed to VARCHAR) if the column is not empty; no limitations exist for empty columns.

Note 

Only the NOT NULL constraint can be added to a table column using the MODIFY clause of the ALTER TABLE statement. All other constraints can be added, dropped, modified, and disabled using ADD, DROP, DISABLE, ENABLE, and MODIFY CONSTRAINT clauses.

Here are some examples. The column PHONE_CATEGORY_S that we added to PHONE table in our recent example is CHAR(15). We are aware that the CHAR data type is best used when we know exactly how many characters will be stored in the column; otherwise VARCHAR2 is a better choice. Also, we decided to increase the maximum column size to 20 characters because we might want to store larger strings there; but for now we want the default string BUSINESS to be populated in this column for all new rows. We also decided we don't want any NULL values in the PHONE_ PRIMARY_S column.

The following statement successfully accomplishes this task:

ALTER TABLE PHONE MODIFY PHONE_PRIMARY_S CHAR(1) NOT NULL  MODIFY PHONE_CATEGORY_S  VARCHAR2(20) DEFAULT 'BUSINESS';

Note 

This statement works because when we added PHONE_PRIMARY_S column we also specified the default value of Y, so it does not contain any NULL values. Unlike ALTER TABLE ... ADD the above ALTER TABLE ... MODIFY statement does not populate default values for existing columns.

Removing table columns

Now imagine the business rules have changed again, and the columns you added and modified in previous examples are no longer needed. The following statement removes PHONE_PRIMARY_S and PHONE_CATEGORY_S columns from PHONE table:

ALTER TABLE PHONE DROP (PHONE_PRIMARY_S, PHONE_CATEGORY_S);

Note 

When you drop a column all indexes and/or constraints associated with the column (if any) are also dropped. If the column to be dropped is a parent key of a nontarget column or if a check constraint references both the target and nontarget columns, CASCADE CONSTRAINTS must be specified, or Oracle generates an error.

Modifying constraints

The MODIFY CONSTRAINT clause enables you to change the existing column or table constraints' status of INITIALLY IMMEDIATE to INITIALLY DEFERRED assuming the constraints were created with keyword DEFERRABLE. Assuming Chapter 4 examples, it would probably make sense to issue the following command after the data load is complete:

ALTER TABLE SALESMAN MODIFY CONSTRAINT chk_salesstatus INITIALLY IMMEDIATE;

The constraint behavior is now indistinguishable from one of a nondeferrable constraint; that is, the constraint is checked after each DDL statement. When you want to perform another data load, the following statement changes the constraint's behavior again:

ALTER TABLE SALESMAN MODIFY CONSTRAINT chk_salesstatus INITIALLY DEFERRED;

Creating new constraints

As with new columns, you might need to create new constraints on the existing table. The situations are usually similar to those when you might need new table columns — for example, when implementing new business rules or amending bad initial designs. For example, you might have realized the salesman's code in the SALESMAN table must be unique. The following statement accomplishes the task:

ALTER TABLE salesman ADD CONSTRAINT uk_salesmancode UNIQUE (salesman_code_s); 

And here are examples of implementing referential integrity constraints on tables created in Chapter 4:

ALTER TABLE customer ADD CONSTRAINT fk_cust_payterms FOREIGN KEY (cust_paytermsid_fn) REFERENCES payment_terms (payterms_id_n); 

ALTER TABLE customer ADD CONSTRAINT fk_cust_salesman FOREIGN KEY (cust_salesmanid_fn) REFERENCES salesman (salesman_id_n); 

ALTER TABLE order_line ADD CONSTRAINT fk_ordline_ordhdr FOREIGN KEY (ordline_ordhdrid_fn) REFERENCES order_header (ordhdr_id_n); 

ALTER TABLE order_line ADD CONSTRAINT fk_ordline_product FOREIGN KEY (ordline_prodid_fn) REFERENCES product (prod_id_n);

Cross-References 

For the full list of ACME tables, indexes, and constraints, refer to Appendix B.

Removing constraints

ALTER TABLE enables you to remove column or table constraints. For example, to remove the unique constraint you just created, use

ALTER TABLE SALESMAN DROP CONSTRAINT uk_salesmancode;

Disabling and enabling constraints

In some situations you might want to be able to defer constraint's action:

ALTER TABLE SALESMAN DISABLE CONSTRAINT chk_salesstatus;     Some data load action here...     ALTER TABLE SALESMAN ENABLE CONSTRAINT chk_salesstatus; 

Note 

As you already know, you can disable/enable constraints only if your table was created with DEFERRABLE constraints. Otherwise you would have to drop and re-create constraints or do some other workarounds described in Chapter 4.

Tip 

You can also enable or disable all triggers (special database objects discussed in Chapter 14) associated with the specified table using ENABLE | DISABLE ALL TRIGGERS clause.

Renaming a table

You can change the name of a table using a RENAME TO clause. For example, if you (or your boss) decided that name SALESMAN is not politically correct, you can change it to SALESPERSON using this command:

ALTER TABLE SALESMAN RENAME TO SALESPERSON;

Please keep in mind that any programs (including your PL/SQL procedures, functions, packages, and triggers that refer the renamed table) will no longer be valid after this change. You would have to find and change all references to the SALESMAN table in your code and recompile the programs.

So, you've found out that's too much of an effort, so you decided to change the name back. No problem:

ALTER TABLE SALESPERSON RENAME TO SALESMAN;

DB2 UDB 8.1

The DB2 ALTER TABLE statement allows you to add columns to a table, add or drop constraints, change the length of a column (VARCHAR only), modify identity column properties and summary table options, alter physical attributes, and more. The syntax (simplified for the purpose of this book) is:

ALTER TABLE [<qualifier>.]<table_name> [ADD COLUMN <column_definition>],... | [ALTER COLUMN [SET DATATYPE VARCHAR <newlength_gt_existinglength>] |               [<identity_column_options>]  ] | [ADD CONSTRAINT <constraint_definition>] | [DROP {PRIMARY KEY |        [FOREIGN KEY | UNIQUE | CHECK | CONSTRAINT] <constraint_name>       } ] | [<physical_options>] | [SET SUMMARY AS {DEFINITION ONLY | <summary_table_definition>}] 

Adding new columns to a table

The statement below adds two new columns to PHONE:

ALTER TABLE PHONE ADD PHONE_PRIMARY_S  CHAR(1) DEFAULT 'Y'                      CHECK (PHONE_PRIMARY_S IN ('Y', 'N')) ADD PHONE_CATEGORY_S CHAR(15);

Increasing VARCHAR column size

DB2 has very limited options for modifying existing columns. Virtually all you can do is to increase the size of an existing VARCHAR column:

ALTER TABLE CUSTOMER ALTER COLUMN CUST_ALIAS_S SET DATA TYPE VARCHAR (20)

The statement above increases the size of CUST_ALIAS_S from VARCHAR(15) to VARCHAR(20).

Note 

DB2 would not let you decrease the size of a VARCHAR column as well as altering the size of any other data type columns or changing a column data type.

Modifying identity column options

You can modify the options for an existing identity column. Assuming you've created the table PAYMENT_TERMS with PAYTERMS_ID_N as an identity column (see Chapter 4), the following statement alters the identity column option in such a way so it would cycle starting with 100 after the maximum value of 500 is reached:

ALTER TABLE payment_terms ALTER COLUMN payterms_id_n SET CYCLE SET MAXVALUE 500 RESTART WITH 100

Note 

This statement would fail if PAYTERMS_ID_N column was not created as an identity column.

Creating new constraints on an existing table

You can add PRIMARY KEY, UNIQUE, CHECK or referential integrity constraints using the ALTER TABLE statement. Some examples follow:

ALTER TABLE salesman ADD CONSTRAINT uk_salesmancode UNIQUE (salesman_code_s) 

ALTER TABLE customer ADD  CONSTRAINT fk_cust_payterms FOREIGN KEY (cust_paytermsid_fn) REFERENCES payment_terms (payterms_id_n) 

ALTER TABLE customer ADD  CONSTRAINT fk_cust_salesman FOREIGN KEY (cust_salesmanid_fn) REFERENCES salesman (salesman_id_n) 

ALTER TABLE order_line ADD  CONSTRAINT fk_ordline_ordhdr FOREIGN KEY (ordline_ordhdrid_fn) REFERENCES order_header (ordhdr_id_n) 

ALTER TABLE order_line ADD  CONSTRAINT fk_ordline_product FOREIGN KEY (ordline_prodid_fn) REFERENCES product (prod_id_n)

Removing constraints

The ALTER TABLE ... DROP CONSTRAINT statement deletes an existing constraint. For example, to remove the unique constraint you've just created, use

ALTER TABLE SALESMAN DROP CONSTRAINT uk_salesmancode;

Altering summary table options

You can alter summary table options; for example, assuming we created ORDERLINE_SUMMARY table (Chapter 4), this statement changes it from INITIALLY DEFERRED REFRESH DEFERRED to DEFINITION ONLY:

ALTER TABLE orderline_summary SET SUMMARY AS DEFINITION ONLY; 

MS SQL Server 2000

MS SQL Server allows you to change a data type and collation sequence for existing columns, add new and drop existing columns, add or drop constraints, and disable or enable table triggers:

ALTER TABLE [<qualifier>.]<table_name> (   [ALTER COLUMN <column_name>                  <new_datatype> [<size1>[,<size2>]   ]   [COLLATE <collation_name>] |   [ADD <new_column_definition> [<column_constraint>,...]] |   [DROP COLUMN <column_name>,...]   [ADD <table_constraint>,...] |   [DROP CONSTRAINT <constraint_name>] |   [{ENABLE | DISABLE} TRIGGER {ALL | <trigger_name>,...}] )

Cross-References 

Triggers are explained in Chapter 14.

Adding new columns to a table

You can add new columns to a table using the same syntax for column definitions as in MS SQL Server's CREATE TABLE statement. Collation sequences, defaults, identity properties, and constraints can also be specified. The statement below adds two new columns to PHONE:

ALTER TABLE PHONE ADD PHONE_PRIMARY_S   CHAR(1) DEFAULT 'Y'                       CONSTRAINT chk_phoneprim                       CHECK (PHONE_PRIMARY_S IN ('Y', 'N')), PHONE_CATEGORY_S  CHAR(15)

Modifying existing columns

The rules of modifying existing columns in MS SQL Server are different from what either Oracle or DB2 would allow you to do. For example, you cannot change the data type of a column on which a constraint is based or give it a default value. You also are unable to alter columns with TEXT, NTEXT, IMAGE, or TIMESTAMP data types. Some more restrictions apply; see vendor's documentation for details.

Unlike in Oracle, you can decrease the size of a nonempty column as long as all existing data fits the new size. For example, MS SQL Server would let you decrease the size of the PHONE_TYPE_S column from VARCHAR(20) to VARCHAR(10) or even VARCHAR(5) because the longest string stored in this column PHONE is only five characters long:

ALTER TABLE PHONE ALTER COLUMN PHONE_TYPE_S VARCHAR(5)

However, the RDBMS returns an error if you try to decrease the column size to maximum four characters:

ALTER TABLE PHONE ALTER COLUMN PHONE_TYPE_S VARCHAR(4)     Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated.

MS SQL Server behaves similarly with numeric columns. You can decrease the scale, but only down to the size of the biggest column value. The following statement changes the data type of the ORDLINE_ORDQTY_N column from INTEGER to NUMERIC(3):

ALTER TABLE ORDER_LINE ALTER COLUMN ORDLINE_ORDQTY_N NUMERIC(3)

But changing it to NUMERIC(2) is not going to work because the largest current value in this column happens to be 700 in our ACME database:

ALTER TABLE ORDER_LINE ALTER COLUMN ORDLINE_ORDQTY_N NUMERIC(2)     Server: Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.

Removing table columns

Removing table columns in MS SQL Server is more restrictive than in Oracle. For example, a column cannot be dropped if it is used in an index or in a constraint; associated with a default; or bound to a rule. The statement below fails because the column PHONE_PRIMARY_S is used in check constraint CHK_PHONEPRIM and is also associated with a default:

ALTER TABLE PHONE DROP COLUMN PHONE_PRIMARY_S, PHONE_CATEGORY_S     Server: Msg 5074, Level 16, State 1, Line 1 The object 'DF__PHONE__PHONE_PRI__3B0BC30C' is dependent on column 'PHONE_PRIMARY_S'. Server: Msg 5074, Level 16, State 1, Line 1 The object 'chk_phoneprim' is dependent on column 'PHONE_PRIMARY_S'. Server: Msg 4922, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN PHONE_PRIMARY_S failed because one or more objects access this column.

The following statement nonetheless succeeds because the PHONE_CATEGORY_S column alone is not the subject for any of the previous limitations:

ALTER TABLE PHONE DROP COLUMN PHONE_CATEGORY_S

Creating and removing constraints

The syntax to create new constraints or remove existing constraints in MS SQL Server is no different from one in Oracle's and DB2:

ALTER TABLE salesman ADD CONSTRAINT uk_salesmancode UNIQUE (salesman_code_s); ALTER TABLE SALESMAN DROP CONSTRAINT uk_salesmancode;

DROP TABLE statement

Because tables occupy physical space, for the sake of efficiency it is important to be able to get rid of them as soon as they are no longer needed. The DROP TABLE statement works pretty much the same way in all "big three" databases. It releases the physical storage for the deleted table as well as for any indexes on its columns. The definitions for the table, its related indexes, integrity constraints, and triggers are removed from the database data dictionary. All views become invalid and have to be either changed to delete all references to the removed table or manually deleted from the database.

Caution 

DROP TABLE is a part of DDL and is therefore irreversible. Specifically, it is committed to the database immediately without possibility of a rollback. Use it with extreme care.

Following is the syntax for the DROP TABLE statement.

Oracle 9i

DROP TABLE [<qualifier>.]<table_name> [CASCADE CONSTRAINTS];

The optional CASCADE CONSTRAINTS clause lets you drop a table with enforced referential integrity constraints; the constraints will also be dropped. If the clause is omitted and such referential integrity constraints exist, Oracle generates an error and the table would not be dropped:

SQL> DROP TABLE CUSTOMER; DROP TABLE CUSTOMER            * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys

The problem is that CUSTOMER table is referenced by ADDRESS, PHONE, and ORDER_HEADER tables (constraints FK_ADDR_CUST, FK_PHONE_CUST, and FK_ORDHDR_CUSTOMER). To drop CUSTOMER table and the above constraints you could use this SQL statement:

SQL> DROP TABLE customer CASCADE CONSTRAINTS;     Table dropped. 

DB2 UDB 8.1

DROP TABLE [<qualifier>.]<table_name>

In addition to all objects listed here (indexes, constraints, and triggers), all summary tables referencing the table to be removed are also dropped as well as all related referential integrity constraints:

db2 => DROP TABLE customer DB20000I  The SQL command completed successfully. 

This unforgiving behavior could spell a disaster resulting in unintentional loss of data, so be especially cautious when working with the DROP TABLE statement in DB2.

MS SQL Server 2000

DROP TABLE [<qualifier>.]<table_name>

Unlike Oracle, MS SQL Server would not let you drop a table in which a primary key is referenced by any foreign key. To drop the table CUSTOMER you would have to drop the referential integrity constraints FK_ADDR_CUST, FK_PHONE_CUST, and FK_ORDHDR_CUSTOMER first:

1> DROP TABLE CUSTOMER 2> GO Msg 3726, Level 16, State 1, Server PD-TS-TEST1, Line 1 Could not drop object 'CUSTOMER' because it is referenced by a FOREIGN KEY constraint. 1> ALTER TABLE address 2> DROP CONSTRAINT fk_addr_cust 3> ALTER TABLE phone 4> DROP CONSTRAINT fk_phone_cust 5> ALTER TABLE order_header 6> DROP CONSTRAINT FK_ORDHDR_CUSTOMER 7> GO 1> DROP TABLE CUSTOMER 2> GO 1>