8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

Renaming Columns And Constraints

In addition to renaming tables and indexes Oracle9i Release 2 allows the renaming of columns and constraints on tables. In this example once the the TEST1 table is created it is renamed along with it's columns, primary key constraint and the index that supports the primary key.

-- Create a test table with a primary key.
create table test1 (
  col1  number(10) not null, 
  col2  varchar2(50) not null);

alter table test1 add (
  constraint test1_pk primary key (col1));


-- Describe the table.
desc test1
 Name                 Null?    Type
 -------------------- -------- --------------------
 COL1                 NOT NULL NUMBER(10)
 COL2                 NOT NULL VARCHAR2(50)

SQL>


-- Display the constraint name.
select constraint_name
from   user_constraints
where  table_name      = 'TEST1'
and    constraint_type = 'P';

CONSTRAINT_NAME
------------------------------
TEST1_PK

1 row selected.

SQL>


-- Display the index name.
select index_name, column_name
from   user_ind_columns
where  table_name = 'TEST1';

INDEX_NAME            COLUMN_NAME
--------------------  --------------------
TEST1_PK              COL1

1 row selected.

SQL>


-- Rename the table, columns, primary key and supporting index.
alter table test1 rename to test;

alter table test rename column col1 to id;

alter table test rename column col2 to description;

alter table test rename constraint test1_pk to test_pk;

alter index test1_pk rename to test_pk;


-- Describe the table.
desc test
 Name                 Null?    Type
 -------------------- -------- --------------------
 ID                   NOT NULL NUMBER(10)
 DESCRIPTION          NOT NULL VARCHAR2(50)

SQL>


-- Display the constraint name.
select constraint_name
from   user_constraints
where  table_name      = 'TEST'
and    constraint_type = 'P';

CONSTRAINT_NAME
--------------------
TEST_PK

1 row selected.

SQL>


-- Display the index name.
select index_name, column_name
from   user_ind_columns
where  table_name = 'TEST';

INDEX_NAME            COLUMN_NAME
--------------------  --------------------
TEST_PK               ID

1 row selected.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.