Oracle ALTER TABLE MODIFY Column

Summary: in this tutorial, you will learn how to use the Oracle ALTER TABLE MODIFY column statement to change the definition of existing columns.

Introduction to Oracle ALTER TABLE MODIFY Column statement #

To change the definition of a column in a table, you use the ALTER TABLE MODIFY column statement.

Here’s the basic syntax:

ALTER TABLE table_name 
MODIFY column_name action;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of table from which you want to modify a column in the ALTER TABLE clause.
• Second, prorivde the column name and action you want to perform in the MODIFY clause.

Oracle allows you to perform many actions:

• Modifying the visiblity of a column.
• Adding / removing NOT NULL constraints.
• Making the column’s size bigger or smaller.
• Changing the default value of a column.
• Modifying the expression of virtual columns.

Oracle lets you to modify multiple columns at once using the following syntax:

ALTER TABLE table_name
MODIFY (
    column1 action1,
    column2 action2,
    ...
);Code language: SQL (Structured Query Language) (sql)

Oracle ALTER TABLE MODIFY column examples #

First, create a new table calledaccounts :

CREATE TABLE accounts (
    account_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(25) NOT NULL,
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(100),
    phone VARCHAR2(12),
    full_name VARCHAR2(51) GENERATED ALWAYS AS( 
            first_name || ' ' || last_name
    ),
    PRIMARY KEY(account_id)
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the accounts table:

INSERT INTO accounts(first_name,last_name,phone)
VALUES('Trinity',
       'Knox',
       '410-555-0197');


INSERT INTO accounts(first_name,last_name,phone)
VALUES('Mellissa',
       'Porter',
       '410-555-0198');


INSERT INTO accounts(first_name,last_name,phone)
VALUES('Leeanna',
       'Bowman',
       '410-555-0199');
Code language: SQL (Structured Query Language) (sql)

Third, verify the inserts by using the following SELECT statement:

SELECT * FROM accounts;Code language: SQL (Structured Query Language) (sql)

Modify the column’s visibility #

Starting from Oracle 12c, you can define table columns as invisible or visible. Invisible columns are not available for the query like:

SELECT * FROM table_name;Code language: SQL (Structured Query Language) (sql)

Or:

DESCRIBE table_name;Code language: SQL (Structured Query Language) (sql)

However, you can query the invisible columns by specifying them explicitly in the query:

SELECT
    invisible_column_1,
    invisible_column_2
FROM
    table_name;Code language: SQL (Structured Query Language) (sql)

Table columns are visible by default. You can define an invisible column when you create the table or using ALTER TABLE MODIFY column statement.

For example, the following statement makes the full_name column invisible:

ALTER TABLE accounts 
MODIFY full_name INVISIBLE;Code language: SQL (Structured Query Language) (sql)

The following statement returns data from all columns of the accounts table except the full_name column:

SELECT * FROM accounts; Code language: SQL (Structured Query Language) (sql)

This is because the full_name column is invisible.

To change a column from invisible to visible, you use the following statement:

ALTER TABLE accounts 
MODIFY full_name VISIBLE;Code language: SQL (Structured Query Language) (sql)

Adding / removing NOT NULL constraints #

The following statement adds the NOT NULL constraint to the email column:

ALTER TABLE accounts 
MODIFY email VARCHAR2( 100 ) NOT NULL;Code language: SQL (Structured Query Language) (sql)

However, Oracle issued the following error:

SQL Error: ORA-02296: cannot enable (OT.) - null values foundCode language: SQL (Structured Query Language) (sql)

When you change a column from nullable to non-nullable, you must ensure that the existing data meets the new constraint.

To fix this, we update the values for the email column first:

UPDATE 
    accounts
SET 
    email = LOWER(first_name || '.' || last_name || '@oracletutorial.com');Code language: SQL (Structured Query Language) (sql)

Note that the LOWER() function converts a string to lowercase.

And then change the column’s constraint:

ALTER TABLE accounts 
MODIFY email VARCHAR2( 100 ) NOT NULL;Code language: SQL (Structured Query Language) (sql)

Now, it is working as expected.

Widen or shorten the size of a column example #

Suppose you want to add international code to the phone numbers. Before doing it, you must make tthe sizze of the phone column longer:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 15 );Code language: SQL (Structured Query Language) (sql)

Now, you can update the phone numbers:

UPDATE
    accounts
SET
    phone = '+1-' || phone;Code language: SQL (Structured Query Language) (sql)

The following statement verifies the update:

SELECT
    *
FROM
    accounts;
Code language: SQL (Structured Query Language) (sql)

To shorten the size of a column, you make sure that all data in the column fits the new size.

For example, we try to shorten the size of the phone column down to 12 characters:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );Code language: SQL (Structured Query Language) (sql)

Oracle Database issued the following error:

SQL Error: ORA-01441: cannot decrease column length because some  value is too bigCode language: SQL (Structured Query Language) (sql)

To fix this, first, we should remove the international code from the phone numbers:

UPDATE
    accounts
SET
    phone = REPLACE(
        phone,
        '+1-',
        ''
    );
Code language: SQL (Structured Query Language) (sql)

The REPLACE() function replaces a substring with a new substring. In this case, it replaces the ‘+1-‘ with an empty string.

And then shorten the size of the phone column:

ALTER TABLE accounts 
MODIFY phone VARCHAR2( 12 );Code language: SQL (Structured Query Language) (sql)

Modifying virtual columns #

Suppose you want the full name to be in the following format:

last_name, first_nameCode language: SQL (Structured Query Language) (sql)

To do this, you can change the expression of the virtual column full_name as follows:

ALTER TABLE accounts 
MODIFY full_name VARCHAR2(52) 
GENERATED ALWAYS AS (last_name || ', ' || first_name);Code language: SQL (Structured Query Language) (sql)

The following statement verifies the modification:

SELECT * FROM accounts;
Code language: SQL (Structured Query Language) (sql)

Modify the default value of a column #

Let’s add a new column named status to the accounts table with default value 1.

ALTER TABLE accounts
ADD status NUMBER( 1, 0 ) DEFAULT 1 NOT NULL ;Code language: SQL (Structured Query Language) (sql)

Once you execute the statement, the values in the status column are set to 1 for all existing rows in the accounts table.

To change the default value of the status column to 0, you use the following statement:

ALTER TABLE accounts 
MODIFY status DEFAULT 0;Code language: SQL (Structured Query Language) (sql)

We can add a new row to the accounts table to check whether the default value of the status column is 0 or 1:

INSERT INTO accounts ( first_name, last_name, email, phone )
VALUES ( 'Julia',
         'Madden',
         '[email protected]',
         '410-555-0200' );Code language: SQL (Structured Query Language) (sql)

Query data from the accounts table:

SELECT * FROM accounts;
Code language: SQL (Structured Query Language) (sql)

The value in the status column for the account with id 4 is 0.

Summary #

• Use the Oracle ALTER TABLE MODIFY column statement to change the definition of existing columns in a table.

Quiz #