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!


Possibly related:


Tagged , | Post a Comment