Database Tutorial

Table Maintenance

You may like to think your done once you design a table and implement it in your database-centric application. Unfortunately, that’s not true as a rule.

Table definitions evolve. Table definitions may evolve as follows:

• You may add a NOT NULL constraint to an optional column or remove a NOT NULL constraint from a mandatory column.
• You may add a previously non-key column into the natural key.
• You may discover you have a second subject in the table and remove it to change the tables normalization level from 1NF to 2NF or 2NF to 3NF.
• You may discover a variable length string data type is too small and increase its precision or size.
• You may discover a column’s data type is inappropriate and have to change it, like moving a VARCHAR2 string to a CLOB string when the string became too long.
• You may need to add or modify a CHECK constraint.
• You may need to add temporal columns to a table, which are typical start and end dates or timestamps to ensure uniqueness of data among the rows.
• You may need to change a column name, which may impose cascading changes on other table designs.

Tables also require periodic evaluation. You need to ensure that foreign key column values both map to the right primary key columns and the correct primary key values. Changes in table data of any table that a foreign key references may require you to update the dependent foreign key values.

Occasionally, you may need to drop and recreate a table with a new storage clause. During that table maintenance you may also change the order of columns, which relies on good coding practices when writing INSERT statements. That practice requires that all INSERT statements list the columns their values will be stored.