Oracle ALTER TABLE MODIFY Column

ALTER TABLE MODIFY statement is used for modifying the attributes or changing the definition of existing columns in Oracle database.

Syntax:

The syntax of Oracle ALTER TABLE MODIFY statement is as follows:

ALTER TABLE table_name 
MODIFY column_name modificiation;

Here,

table_name – It is the name of table in which we want to make changes.

column_name – It is the name of column whose definition we want to change.

modification – It is the action or change that we want in the existing the structure.

Some of the modifications that we can perform are as follows:

1. Allow or Disallow NULL values in column
2. Change the size or number of characters/numbers allowed in Column
3. Setting a default value for the column
4. Set an expression for determining the Input value in Column
5. Modify the visibility of column

We can also make modifications to multiple columns at once using this syntax:

ALTER TABLE table_name
MODIFY (
    column_1 modification,
    column_2 modification,
    column_3 modification,
    ...
);

Oracle ALTER TABLE MODIFY column examples

Let’s create a new table, named Workers for these examples:

CREATE TABLE workers (
    worker_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(10) NOT NULL,
    last_name VARCHAR2(10) NOT NULL,
    email VARCHAR2(30),
    location VARCHAR2(20) ,
    full_name VARCHAR2(51) GENERATED ALWAYS AS( 
            first_name || ' ' || last_name
    )
);

Let’s insert some rows into the

INSERT INTO workers(worker_id,first_name,last_name,location)
VALUES(1,
       'Wes',
       'Kao',
       'Texas');

INSERT INTO workers(worker_id,first_name,last_name,location)
VALUES(2,
       'Gagan',
       'Biyani',
       'San Francisco');

INSERT INTO workers(worker_id,first_name,last_name,location)
VALUES(3,
       'Jack',
       'Butcher',
       'New York');

INSERT INTO workers(worker_id,first_name,last_name,location)
VALUES(4,
       'Ray',
       'Dalio',
       'Texas');

INSERT INTO workers(worker_id,first_name,last_name,location)
VALUES(5
       'Kobe',
       'Bryant',
       'Los Angeles');

Let’s use SELECT query to have a look at the table:

SELECT
    *
FROM
    workers;

Result:

1. Allow or Disallow NULL values in column

Let’s modify the column definition of location column to only accept NON-NULL values:

ALTER TABLE workers 
MODIFY email VARCHAR2( 30 ) NOT NULL;

However, Oracle issued the following error:

SQL Error: ORA-02296: cannot enable (OT.) - null values found

We get this error, because the existing column already contains NULL values and before we apply any new constraint, the values in existing column must meet the condition.

Let’s update the locations column first, before altering the column to NOT NULL:

UPDATE 
    workers
SET 
    email = 'NOT AVAILABLE';

Now, that we converted the email of all workers to ‘NOT AVAILABLE, we can alter the column definition and add NOT NULL constraint:

ALTER TABLE workers 
MODIFY email VARCHAR2( 30 ) NOT NULL;

Now, we don’t get any error and from now on, this column won’t accept any NULL values.

2. Change the size or number of characters/numbers allowed in Column

Suppose, we want to increase the size of location column and also want to add the country ID =’US’ as suffix for all values in locations column, for this we use the following query:

ALTER TABLE workers 
MODIFY location VARCHAR2( 25 );

Let’s add a suffix to all values in location column:

UPDATE
    workers
SET
    location = location || '(US)';

Let’s use a SELECT query for seeing the contents of table:

SELECT
    *
FROM
    workers;

Result:

Let’s try to reduce the total number of characters allowed in location column to 12 character.

ALTER TABLE workers 
MODIFY location VARCHAR2( 12 );

Oracle issues an error:

SQL Error: ORA-01441: cannot decrease column length because some  value is too big

Let’s remove the suffix or country codes from the location column to fix this error:

UPDATE
    workers
SET
    location = REPLACE(
        location,
        '(US)',
        ''
    );

The REPLACE function replace the country code with an empty string, thereby reducing the size of column:

Result:

Now, lets try shortening the column again:

ALTER TABLE workers 
MODIFY locations VARCHAR2( 12 );

This time we did not get any error and the size of column has been shortened to 12 characters.

3. Setting a default value for the column

Let’s add a new column to the workers table, named STATUS:

ALTER TABLE workers
ADD status NUMBER( 1, 0 ) DEFAULT 1 NOT NULL ;

Result:

As we can see, the default value for this column is set to 1 for all workers.

Let’s change this to 0:

ALTER TABLE workers 
MODIFY status DEFAULT 0;

Result:

From now on, each row will have default status as 0 instead of 1.

4. Set an expression for determining the Input value in Column

Let’s change the format of email column to the following format:

[email protected]

For this, we need to add a new virtual column named new_email using the following query:

ALTER TABLE workers
ADD new_email VARCHAR2(51) GENERATED ALWAYS AS 
(first_name|| '.' || last_name || '@qurosity.com') ;

Let’s verify:

  SELECT
    *
FROM
    workers;

Result:

We can only use expressions in a virtual column and not a real column. As we can see, new_email column auto-generates the emails of workers.

5. Modify the visibility of column

Oracle 12c comes with a feature that allows us to set the visibility of columns. After setting the column to Invisible we won’t be able to see the columns in SELECT or DESC query.

But if query the invisible columns individually by mentioning them in the query, then we can see them:

SELECT
    invisible_column1,
    invisible_column2
FROM
    table_name;

By default, all columns in table are set to VISIBLE but we can use ALTER TABLE MODIFY to set them to invisible:

Let’s see one query:

ALTER TABLE workers 
MODIFY email INVISIBLE;

The following statement returns data from all columns except the email column from workers table.

SELECT
    *
FROM
    accounts; 

Result:

This is because the email column is invisible.

Let’s set the table back to visible:

ALTER TABLE workers 
MODIFY email VISIBLE;

In this article, we learned how to use Oracle ALTER TABLE MODIFY statement to modify the attributes and definitions of existing columns.