Name already been used by an existing constraint after renaming the table and creating a new one

Question

I would like to know why, if I alter a table name to different name and then create a table with the same name, I am getting the error "name already been used by existing constraint".

This is the create table code

   create table abc
  (
       num number (4),
       name varchar2 (50),
      constraint abc primary key (num, name)
 );

This is the alter table code

 alter table abc rename to cba;

This is the code to recreate the table

   create table abc
  (
       num number (4),
       name varchar2 (50),
      constraint abc primary key (num, name)
 );

Answer 1

After renaming the table, rename the constraint and the index as well:

alter table cba rename constraint abc to cba;
alter index abc rename to cba;

Answer 2

Whenever you create a table with constraints in Oracle, an entry is made into the user_constraints table and if constraints involve creation of an index (like in this case, primary key constraint also creates a clustered index), an index is also created on the table which you can find querying user_indexes.

select * from user_constraints where table_name='table name';

select * from user_indexes where table_name='table_name';

You can also query using constraint name to check if constraints still exist:

select * from user_constraints where constraint_name='abc';

select * from user_indexes where index_name='abc';

When a table is dropped, it is not purged immediately. It resides in recycle bin. Hence we sometimes observe that the constraints are not affected, which results in an error when same constraint names are used within short time. While querying the user_constraints or user_indexes table, you would observe that table name is some random string starting with 'BIN$'

Hence we may need to explicitly drop the constraints and indexes, when the error pops up.

Another approach is to completely purge the table :

drop table table_name purge;

This however means that you cannot recover the table later using flashback.