Add or Remove Columns

Introduction to Database Tables

  • A database table is constructed with one or several columns.

  • It is essential to note that a database table cannot exist without any columns.

Dynamic Nature of Data Content

  • The data content of a table is not static; it can evolve over time based on business requirements.

  • Column definitions may also need to change over time.

  • Possible reasons for altering columns include:

    • Adding columns to satisfy new business requirements.

    • Removing columns for corporate privacy reasons, optimizing table space, or correcting past mistakes.

Naming Rules for Table Columns

  • The naming conventions for table columns are similar to those of other database objects:

    • Column names must start with a letter.

    • They can be up to 128 characters long.

    • The remaining characters may consist of letters, numbers, and the special characters: underscore (_), dollar sign ($), and pound sign (#).

  • Mixed-case column names can be created by surrounding them in double quotes.

    • Example: "MixedCaseColumn"

    • Note: Once defined in mixed case, the column name must continually be referenced using double quotes.

Adding Columns to a Table

  • To add a column to an existing table, use the ALTER TABLE statement followed by ADD COLUMN.

  • The syntax is clear and straightforward:

    • Example syntax: ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];

  • Example:

    • Adding a column middle_name to the employees table:

    • Command: ALTER TABLE employees ADD COLUMN middle_name VARCHAR(20) NOT NULL;

    • Here, VARCHAR(20) specifies the data type, and NOT NULL indicates that the column can be left unpopulated.

    • Inclusion of NOT NULL clarifies the intention to allow null values, which is the default behavior.

Dropping Columns from a Table

  • When a column is no longer needed, it can be removed using the ALTER TABLE ... DROP COLUMN command.

  • Syntax to drop a column:

    • ALTER TABLE table_name DROP COLUMN column_name;

  • Drop Multiple Columns:

    • More than one column can be dropped in a single command.

  • Error Handling:

    • If a user has open transactions on the table (i.e., hasn't committed or rolled back), the drop operation will fail and return an error (ORA-00054).

  • Example:

    • Dropping the middle_name column and trying to drop the department_id column from the departments table.

    • If there’s a foreign key constraint referencing department_id, an error will occur.

Foreign Key Constraints and Errors

  • Foreign Key Dependencies:

    • If a column (like department_id) is referenced by a foreign key in another table (such as the employees table), the column cannot simply be dropped.

  • Cascade Option:

    • The cascade option can be used if multiple references need to be removed, but it is dangerous if many tables reference this key.

  • Primary Keys:

    • Since department_id is a primary key, dropping it would result in a loss of uniqueness guarantee in the departments table.

  • Resolution:

    • Instead of dropping, renaming the department_id column to comply with naming conventions (e.g., depd_id).

    • Syntax to rename a column: ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

    • Successfully renaming the column retains the primary key constraint.

Modifying Column Definitions

  • To change a column's data type or to add constraints, use ALTER TABLE ... MODIFY.

  • Modifying a Column:

    • You can modify the data type of columns, extend varchar length, or adjust constraints.

  • Example:

    • Changing a column's type from numeric to character or widening a VARCHAR column.

  • Caution:

    • When changing a column's data type, pay attention to potential conversion issues, especially when converting from larger character types (like VARCHAR) to numbers.

Conclusion

  • Table definitions are dynamic and are not static post-creation.

  • Altering columns (adding, dropping, renaming, or modifying) necessitates careful consideration of existing foreign key constraints and dependencies to ensure data integrity and system functionality.