PL/SQL ALTER TABLE

Definition of PL/SQL ALTER TABLE

PL/SQL alter table command is used to make the changes in the table structure. It is used to add, modify and drop the columns of the table. One can also change the name of the table using the ALTER TABLE command. This command provides the flexibility to the programmer or DBA to make the changes in the table structure without hampering the table data. It also allows users to modify or add multiple columns at a time by providing the necessary details about the columns that need to be modified or added. It is the most widely and specific command when the table schema changes need to be done.

Syntax:

As described above, alter table command in Oracle PL/SQL is used to add, delete/ drop and modify the columns in the table. It can also be used to rename the table name. Below given is the basic syntax of each of the task which the alter table can perform in PL/SQL:

Add Column using alter table command:

ALTER TABLE table_name
ADD
column_name column_definition;

Adding multiple Columns using alter table command:

ALTER TABLE table_name
ADD
(column_name_1 column_definition_1,
column_name_2 column_definition_2,
column_name_3 column_definition_3,
..
column_name_n column_definition_n);

where,

• table_name: It is the name of the table in which we want to add the column.
• column_name: It is the name of the column which is to be added to the table.
• column_definition: It is the data type and the other constraints in the column which is to be added in the table.

Drop/ Delete the Column using alter table command:

ALTER TABLE table_name
DROP COLUMN
column_name;

where,

• table_name: It is the name of the table from which we want to delete the column.
• column_name: It is the name of the column which is to be deleted from the table.

Modify the Column using alter table command:

ALTER TABLE table_name
MODIFY
column_name column_type;

Modify multiple Columns using alter table command:

ALTER TABLE table_name
MODIFY
(column_name_1 column_definition_1,
column_name_2 column_definition_2,
column_name_3 column_definition_3,
..
column_name_n column_definition_n);

where,

• table_name: It is the name of the table in which we want to modify the column.
• column_name: It is the name of the column which is to be modified in the table.
• column_type: It is the data type of the column that is to be modified in the table.

Modifying the Column name using alter table command:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

where,

• table_name: It is the name of the table in which we want to modify the column name.
• old_column_name: It is the name of the old column whose name is to be modified in the table.
• new_column_name: It is a new column name that will be reflected in the table.

Rename the table using alter table command:

ALTER TABLE table_name
RENAME TO
new_table_name;

where,

• table_name: It is the name of the table which we want to rename.
• new_table_name: It is the new name of the table.

How does an alter table work in PL/SQL?

ALTER TABLE command works simply by making the changes at the schema level of the table without hampering the table data. It is important to learn the clauses used to make any specific modifications. Some of the important points that need to be kept in mind while working on alter table in PL/SQL are given below:

1. ALTER TABLE command is used if the user wants to modify the structure of the table.

2. The user can add or modify a single column as well as multiple columns at a time in the table.

3. ALTER TABLE ADD is used to add a new column or columns in the table. These column/ columns will be added at the end of the table.

4. In order to add the column/ columns in the table, the user needs to specify the column_name along with its data type and the other constraints with it.

5. An error will be thrown if the user tries to add the column/ columns that already exist in the table, modify the columns that do not exist, drop the column that does not exist in the table, etc.

6. In order to modify the column/ columns, MODIFY clause is used.

7. In order to delete the column in the table DROP clause is used.

8. In order to rename the table name, the RENAME TO clause is used.

9. In order to rename the name of the existing column, the RENAME TO clause is used.

Examples

Below given are some of the examples showing the practical usage of alter table in PL/SQL code:

Consider the below-given table:

CREATE TABLE customers_data
( cust_id number(10) NOT NULL,
cust_name varchar2(50) NOT NULL,
cust_pin_code varchar2(10),
CONSTRAINT cust_pk PRIMARY KEY (cust_id)
);

Present Data in the table:

Example #1

Adding column ‘cust_address’ with ‘varchar2(100) data type in the above table:

ALTER TABLE customers_data ADD cust_address varchar2(100);

Output:

Example #2

Modifying the column ‘cust_name’ and ‘cust_pin_code’ data type in the above table:

ALTER TABLE customers_data MODIFY
(
cust_name varchar2(80),
cust_pin_code varchar2(20)
);

Output:

The table data is not hampered and remains the same:

Example #3

Changing the column name ‘cust_pin_code’ to ‘custoner_zipcode’ in the above table:

ALTER TABLE customers_data RENAME COLUMN cust_pin_code TO customer_zipcode;

Output:

Example #4

Dropping the column ‘cust_address’ added before from the above table:

ALTER TABLE customers_data DROP COLUMN cust_address;

Output:

The table data of the other columns remain unchanged except removing the column ‘cust_address.’

Example #5

Renaming the above-created table name from customers_data to cust_data:

ALTER TABLE customers_data RENAME TO cust_data;

Output:

Data inside the table remains the same. But we need to access the table data using the new table name (i.e. ‘cust_data’):

Conclusion

The above description clearly explains what the alter table is in PL/SQL and how it works. It is one of the most important commands which is used to make changes in the schema of the table. Any changes made related to table stricture are done with the help of ALTER TABLE command. It is important for a programmer to understand these commands clearly in order to make the changes in the schema quickly without any problem.

Recommended Articles

This is a guide to PL/SQL ALTER TABLE. Here we discuss the Definition, syntax, Commands with Examples with code implementation. You may also have a look at the following articles to learn more –

1. PL/SQL Commands
2. Careers in PL/SQL
3. CASE statement in PL/SQL
4. What is PL/SQL?