Technical Note on Exchanging Column Names in a Table


Today, I had a question about how to exchange the names of two (2) columns in a table. Apparently, there was a transposition error in the data loading program.

The simple answer is that you have to rename the columns one at a time. This requires three (3) renaming steps.

The following demonstration is done under 12.1.0.1.

See the documentation at ALTER TABLE for more information

First, create the demonstration table and load some data into it:

--
-- Create a table with the wrong column names
--
drop table wrong_name;
create table wrong_name (
  "ID"
    NUMBER
    CONSTRAINT wrong_name_pk PRIMARY KEY,
  col1
    VARCHAR2(100)
    CONSTRAINT wrong_name_col1_nn NOT NULL
    CONSTRAINT wrong_name_col1_ck CHECK (UPPER(col1) = col1),
  col2
    VARCHAR2(100)
    CONSTRAINT wrong_name_col2_nn NOT NULL
    CONSTRAINT wrong_name_col2_ck CHECK (LOWER(col2) = col2));
--
-- Now insert some test data
--
INSERT INTO wrong_name ("ID", col1, col2)
  VALUES(1, 'OLD COLUMN 1', 'old column 2');
COMMIT;

Now check the values:

--
-- Check Values
--
COLUMN col1 FORMAT A15
COLUMN col2 FORMAT A15
SELECT * FROM wrong_name;

The result is:

        ID COL1            COL2
---------- --------------- ---------------
         1 OLD COLUMN 1    old column 2

Let’s try to exchange both columns at the same time:

--
-- Try to exchange column names at the same time
--
ALTER TABLE wrong_name
  RENAME COLUMN col1 TO col2,
  RENAME COLUMN col2 TO col1;

Unfortunately, the following error message appears in SQL/Developer:

Error starting at line 32 in command:
ALTER TABLE wrong_name
  RENAME COLUMN col1 TO col2,
  RENAME COLUMN col2 TO col1
Error report:
SQL Error: ORA-23290: This operation may not be combined with any other operation
23290. 00000 -  "This operation may not be combined with any other operation"
*Cause:    ALTER TABLE RENAME COLUMN/CONSTRAINT operation was given in
           conjunction with another ALTER TBALE Operation. This is not
           allowed.
*Action:   Ensure that RENAME COLUMN/CONSTRAINT is the only operation
           specified in the ALTER TABLE.

The correct procedure is:

--
-- Exchange column names one at a time
--
ALTER TABLE wrong_name RENAME COLUMN col1 TO col_temp;
ALTER TABLE wrong_name RENAME COLUMN col2 TO col1;
ALTER TABLE wrong_name RENAME COLUMN col_temp TO col2;

Now check the values:

--
-- Check Values
--
COLUMN col1 FORMAT A15
COLUMN col2 FORMAT A15
SELECT * FROM wrong_name;

The result is:

        ID COL2            COL1
---------- --------------- ---------------
         1 OLD COLUMN 1    old column 2

Now we have a problem in that the constraints are applied to the wrong columns. When we try to add some new data as follows, we get an error:

--
-- Now insert some more test data
--
INSERT INTO wrong_name ("ID", col1, col2)
  VALUES(2, 'NEW COLUMN 1', 'new column 2');
COMMIT;

The error message is:

Error report:
SQL Error: ORA-02290: check constraint (TEST_USER.WRONG_NAME_COL2_CK) violated
02290. 00000 -  "check constraint (%s.%s) violated"
*Cause:    The values being inserted do not satisfy the named check

*Action:   do not insert values that violate the constraint.

In this case, the procedure is far more complicated because the constraints are now applied to the wrong columns. To remedy this particular case, we have to drop the constraints; modify the data; and re-create the constraints.

--
-- Remove Constraints
--
ALTER TABLE wrong_name DROP CONSTRAINT wrong_name_col1_ck;
ALTER TABLE wrong_name DROP CONSTRAINT wrong_name_col2_ck;
--
-- Modify data to match new constraints
--
UPDATE wrong_name
  SET col1 = UPPER(col1),
      col2 = LOWER(col2);
COMMIT;
--
-- Create new constraints
--
ALTER TABLE wrong_name ADD CONSTRAINT wrong_name_col1_ck CHECK (UPPER(col1) = col1);
ALTER TABLE wrong_name ADD CONSTRAINT wrong_name_col2_ck CHECK (LOWER(col2) = col2);

Now, we can add data to the table correctly:

--
-- Now insert some more test data
--
INSERT INTO wrong_name ("ID", col1, col2)
  VALUES(2, 'NEW COLUMN 1', 'new column 2');
COMMIT;

And, the contents are now:

        ID COL2            COL1
---------- --------------- ---------------
         1 old column 1    OLD COLUMN 2
         2 new column 2    NEW COLUMN 1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s