Rename column or constraint in Oracle

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

Reader's Comments

1. Lyle B. | 08 Sep 2005 12:59 pm

   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. Eddie Awad | 08 Sep 2005 6:19 pm

   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).