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!
Possibly related:
- Triggers and Constraints
- A binary flag with three values
- The Lazy Developer’s Way to Populate a Surrogate Key
- CURRVAL and NEXTVAL
- Cool Undocumented OVERLAPS Predicate
Tagged constraint, rename | Post a Comment
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;
September 8th, 2005, at 12:59 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](http://www.freelists.org/webpage/oradev) and ask your question(s) there (with more details).
September 8th, 2005, at 6:19 pm #