Modifying Tables

After learning how to create table, we need to learn to modify them. Sometimes we want to make changes to a table we have created for example, we want to add a new column, delete a column, adding a constraint, renaming the entire table name, etc. That is what we call modifying tables.

We use the ALTER TABLE statement to make changes to the table definitions. Below is the syntax for this:

Adding Columns

We don’t need to create a new table all over again in order to have a new column reflected. We can add a column to an existing table. All new columns added appear at the bottom of the table. If you need them in the middle of the existing table, you need to just create a new table. The syntax for creating a table is here:

ALTER TABLE table_name ADD (column_definitions)

Lets add 2 new columns to our faculty table we created previously. The new columns faculty_dean and faculty_location. Both columns are to support up to 100 alpha-numeric characters and it should never be left empty.

ALTER TABLE faculty ADD (faculty_dean VARCHAR2(100) NOT NULL, faculty_location VARCHAR2(100) NOT NULL);

It is optional to wrap column definitions in brackets if you are adding only one column but a must if you’re adding more than one column. For example, lets add the hod column to hold the head of department name to the department table. It should accept only letters not exceeding 100 characters.

ALTER TABLE department ADD hod VARCHAR2(100);

Changing Column Datatype and Column Length

We can make changes to the column definitions i.e. changing one datatype to another or the maximum number of characters for a column (length). We can do this following the syntax below:

ALTER TABLE table_name MODIFY (column_name (new_attributes));

Lets reduce the number of characters for responses column to 400 in the evaluation table.

Note: We can have default values for a column. We can get this using the DEFAULT keyword followed by the default value you want to be used.

Lets change the datatype of created_at column to DATE with a default value of current system date in the evaluation table.

ALTER TABLE evaluation MODIFY (responses VARCHAR2(400), created_at DATE DEFAULT SYSDATE);

It is optional to wrap column definitions in brackets if you are modifying only one column but a must if you’re modifying more than one column.

Renaming Columns

We can as well give the entire column to a new name. The new column name should not be already existing in the table you want to make the change from. Here is the syntax

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Lets rename the title column in the course table to course_title to make it more meaningful.

ALTER TABLE course RENAME COLUMN title TO course_title;

Dropping Columns

Dropping here refers to deleting. We can drop a column from a table. If the column has constraints on it especially the foreign key, we use the CASCADE CONSTRAINTS keyword to remove all of them. Here is the syntax.

ALTER TABLE table_name DROP COLUMN column_name [CASCADE CONSTRAINTS];

Let drop the faculty_location column in the faculty table.

ALTER TABLE faculty DROP COLUMN faculty_location CASCADE CONSTRAINTS;

Sometimes we find out that some columns in the table are never used. For this case, we can label such columns unused and we delete them later as unused columns rather than individual columns. For example, lets label column updated_at in the evaluation table as unused. To do this, we write this:

ALTER TABLE evaluation SET UNUSED COLUMN updated_at;

Then later we can delete it under unused columns.

ALTER TABLE evaluation DROP UNUSED COLUMNS;

Renaming Tables

We can give tables new names. We use the RENAME keyword to rename tables and other database objects. To do this, we use this syntax:

RENAME old_table_name TO new_table_name;

Let us rename the customers to customer_details table we created from the employees table during creation of tables.

RENAME customers TO customer_details;

Making Tables Read-Only

We may decide to disable actions like delete, insert, update, modifying columns, renaming columns, etc on tables. We just need users to view the data in the table but no changes can be made to it. Here, we are making the table read-only. To do that, here is the syntax:

ALTER TABLE table_name READ ONLY;

Lets make the customer_details table read only.

ALTER TABLE customer_details READ ONLY;

Dropping Tables

We sometimes need to delete the entire table from the database. To do this, here is the syntax;

DROP table_name [CASCADE CONSTRAINTS];

We’re not going to delete any table we’ve created. But you can create a sample_table and you give it any columns you wish then you delete it after.

DROP TABLE sample_table;

Summary

1. We use the ALTER TABLE keyword to add, rename, and modify columns. We also use it to make tables read only.

2. We use RENAME keyword to rename tables

3. We use DROP to delete tables from the database.