msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Rename column or constraint in Oracle

Prior to Oracle 9i it was difficult to rename a column or a constraint. In Oracle 9i and above, this can be easily accomplished using the ALTER TABLE command. Here is an example:

CREATE TABLE test (old_col_name VARCHAR2(15))
Processing ...
INSERT INTO test VALUES ('1')
1 row(s) inserted
Processing ...
INSERT INTO test VALUES ('2')
1 row(s) inserted
Processing ...
SELECT * FROM test
Query finished, retrieving results...
OLD_COL_NAME
---------------
1
2

2 row(s) retrieved

Processing ...
ALTER TABLE test 
RENAME COLUMN old_col_name TO new_col_name
Processing ...
SELECT * FROM test
Query finished, retrieving results...
NEW_COL_NAME
---------------
1
2

2 row(s) retrieved

Processing ...
ALTER TABLE test 
ADD CONSTRAINT old_pk_name PRIMARY KEY (new_col_name)
Processing ...
INSERT INTO test VALUES ('2')
*
ORA-00001: unique constraint (APPS.OLD_PK_NAME) violated
Processing ...
ALTER TABLE test 
RENAME CONSTRAINT old_pk_name TO new_pk_name
Processing ...
INSERT INTO test VALUES ('2')
*
ORA-00001: unique constraint (APPS.NEW_PK_NAME) violated
Processing ...
DROP TABLE test

Pretty straight forward stuff, heh!


Filed in Oracle on 15 Jul 05 | Tags: ,


Reader's Comments

  1. |

    I have an insert statement and I occasionally get a unable to insert into table error. Sometimes it works and sometimes it doesn’t. I have taken all the constraints off the Tables. I have tried it with both variables and objects and it does intermittenly.

    My code is as follows.

    declare 
    
    v_kit_num     varchar2(20);
    v_vouch_num_seq    number(4);
    v_vouch_num        varchar2(14);
    v_qty_order        number;
    v_order_date       date;
    v_comment          varchar2(42);
    v_sup_stock_num    varchar2(15);
    
    begin 
    
     v_kit_num := :icb_kats_kits.kit_num;
     v_vouch_num_seq := null;
     v_order_date  := :icb_kats_order.order_date;
     v_vouch_num   :=  :icb_kats_order.vouch_num;
     V_qty_order   := :icb_kats_order.qty_order;
     v_comment     := :icb_kats_order.comment_ord;
     v_sup_stock_num := :icb_kats_order.sup_stock_num;
    
    insert into ict_kats_order1
    (kit_num, vouch_num, qty_order, order_date, sup_stock_num, comment_ord) 
    values 
    (v_kit_num, v_vouch_num, v_qty_order, sysdate, v_sup_stock_num, v_comment);
    
    COMMIT;
    
    END;
    
  2. |

    Hi Lyle,

    Since I do not have the description of your table, or the error number and error description or your Oracle database version, or the scenario that generates the error I can only guess that the error may be related to a violation of column size or datatype.

    Moreover, in order to get more help, I suggest you join the oradev mailing list and ask your question(s) there (with more details).