Add Column in Oracle: ALTER TABLE Guide

1 year ago
Ava Mitchell
2 minutes

To add a new column to a table in Oracle database, you can use the ALTER TABLE statement. The specific steps are as follows:

  1. Please specify the table name for which you want to add a field.
  2. Add a new field using the ALTER TABLE statement, the syntax is as follows:
ALTER TABLE table_name
ADD column_name datatype;

In this case, table_name is the name of the table where the field is to be added, column_name is the name of the field to be added, and datatype is the data type of the new field.

To add a new field named salary to a table called employee with the data type NUMBER, you can use the following statement:

ALTER TABLE employee
ADD salary NUMBER;
  1. If the table that needs to add a column already contains data, you can choose to specify the default value for the column in the ALTER TABLE statement, for example:
ALTER TABLE employee
ADD salary NUMBER DEFAULT 0;

In this way, the new field will automatically be filled with a default value when added, preventing the occurrence of NULL values.

  1. After adding the new field, it may be necessary to update existing data to populate the new field. You can use the UPDATE statement to update the data, for example:
UPDATE employee
SET salary = 5000
WHERE salary IS NULL;

These are the steps and example for adding a field in an Oracle database. Depending on the actual needs, the ALTER TABLE statement can be flexibly used to adjust the table structure.

bannerAds