Oracle Database Alter Table Modify Column

Relational databases are some of the most popular and influential databases in the modern world. They are some of the databases that have been used to power simple applications to complex enterprise-level applications.

Despite them standing the test of time, relational databases are subject to change throughout the application lifecycle. It is, therefore, essential to learn various methods and techniques you can use to modify the properties of a given database at any point in time.

In this article, we will learn how to use the ALTER TABLE MODIFY command to alter the column of a given table. In addition, we will cover how to change various column properties without recreating a table.

Oracle ALTER TABLE MODIFY Command

The ALTER TABLE MODIFY allows you to modify an existing table column without the need to recreate the table.

The command syntax is as shown:

ALTER TABLE table_name

MODIFY column_name action/property;

The command follows a simple syntax that is easy to understand and follow. We start by specifying the table name in which the target column resides.

Next, we specify the column we wish to modify, followed by the action or the property of the column we want to update.

You can also update multiple columns in a single query as shown in the syntax below:

ALTER TABLE table_name
MODIFY (
column_name_1 action/property,
column_nmae_2 action/property
…
);

Oracle database allows you to specify the following actions or properties:

Enable or Disable Column support for NULL values.
Modify column visibility property.
Update the default value of a given column.
Modify the expression of virtual columns.
Expand or Shrink the size of a column.

Example Demonstrations

In the subsequent section, we will explore examples of using the ALTER TABLE MODIFY command in Oracle databases.

We will use a table with values provided in the section below for uniformity. However, if you have an existing table, you can use that to learn how to use this command.

Sample Table and Dataset

The following statements create the sample table and add random data:

createtable orders
(
idnumbernotnull,
customer VARCHAR2(50) notnull,
order_datedate,
product_idnumber,
quantity NUMBER,
price NUMBER,
constraintorder_pk PRIMARY KEY (id)
);

insertinto orders(id, customer, order_date, product_id, quantity, price)
VALUES (1, 'Beatriz', DATE'2022-10-10', 4500, 45, 56);
insertinto orders(id, customer, order_date, product_id, quantity, price)
VALUES (2, 'Ali', DATE'2022-10-10', 5400, 65, 109);
insertinto orders(id, customer, order_date, product_id, quantity, price)
VALUES (3, 'Gabriel', DATE'2022-10-11', 6400, 405, 586);
insertinto orders(id, customer, order_date, product_id, quantity, price)
VALUES (4, 'Beatriz', DATE'2022-10-11', 5800, 55, 550);
insertinto orders(id, customer, order_date, product_id, quantity, price)
VALUES (5, 'Beatriz', DATE'2022-10-12', 4506, 46, 700);
insertinto orders(id, customer, order_date, product_id, quantity, price)
VALUES (6, 'Gabriel', DATE'2022-10-11', 9001, 450, 5600);

The resulting table is as shown:

Example 1 – Oracle Alter Column To Allow Null Values

The following example shows how to use the ALTER TABLE MODIFY command to change the support for NULL values:

ALTER TABLE orders MODIFY customer VARCHAR2(50);

In this case, the query above should update the customer column and allow the ability to add NULL values.

As demonstrated below, we can also perform the polar opposite and prevent a column from accepting NULL values.

ALTER TABLE orders MODIFY product_id NUMBER NOT NULL;

In this case, we tell Oracle to prevent any NULL values in the product_id column.

Example 2 – Oracle Alter Table Modify Column Visibility

Since Oracle 12c, we can define a column as VISIBLE or INVISIBLE. Invisible columns are not accessible for queries such as select *, describe table_name, etc.

To make a column invisible using the alter table modify command, we can execute:

ALTER TABLE orders MODIFY price invisible;

This should change the visibility of the price column. We can demonstrate this by selecting all the columns in the table:

SELECT * FROM orders;

As you can guess, the price column is not shown in the result:

To revert the column visibility, run the command:

ALTER TABLE orders MODIFY price visible;

Example 3 – Oracle Alter Table Update the Column’s Default Value

Let us add a new column with a default value as shown in the query below:

ALTER TABLE orders ADD availability NUMBER(1,0) DEFAULT 0;

In this example, the command above should add a new column called availability holding binary values. It also contains a default value of 0.

If we select the values from the table:

SELECT * FROM orders;

The availability column is populated with zeros as the default value.

TO change the DEFAULT VALUE WITH the ALTER TABLE MODIFY command, run:

ALTER TABLE orders MODIFY availability NUMBER(1,0) DEFAULT 1;

Example 4 – Oracle Alter Table Modify Column Size

We can expand or shrink the size of a given column as shown in the example below:

ALTER TABLE orders MODIFY customer VARCHAR2(100);

In this case, the query above should expand the size of the customer column. You can set a smaller value to shrink the size of a column.

Note that it is good to keep in mind that changing an existing column will mainly affect new values. The changes may not apply to already existing data.

Conclusion

In this article, you discovered the usage of the ALTER TABLE MODIFY command to change various properties of an existing table column in Oracle Databases.