CODEWITHSUNDEEP
sqldatabaseoracle-database
Avi
Avi

Reputation: 1110

Rename a table, then create a table with the same name in oracle

I had a table called 'User' in Oracle I renamed it to 'Account' using the below command. This was successful.

RENAME User TO Account

I then want to create a new table called 'User' with different columns. I tried the following command,

CREATE TABLE User(
    user_id    NUMBER(38, 0)    NOT NULL,
    user_name  VARCHAR2(25)     NOT NULL,
    CONSTRAINT PK_User PRIMARY KEY (user_id)
)
;

But Oracle does not allow me to do so. I get the following error,

Error starting at line : 1 in command -
CREATE TABLE User(
    user_id    NUMBER(38, 0)    NOT NULL,
    user_name  VARCHAR2(25)     NOT NULL,
    CONSTRAINT PK_User PRIMARY KEY (user_id)
)
Error report -
SQL Error: ORA-00955: name is already used by an existing object
00955. 00000 -  "name is already used by an existing object"
*Cause:    
*Action:

What could be the issue? Do I need to run any other command after renaming a table to clear some cache etc.

Edit:

Constraints were renamed successfully after renaming the table

ALTER TABLE Account RENAME CONSTRAINT PK_User TO PK_Account

NOTE: The table names 'Account' and 'User' are used just for example purpose. I understand that 'User' is a reserved keyword.

Upvotes: 2

Views: 1864

Answers (4)

Rene
Rene

Reputation: 10551

If Oracle tells you: "name is already used by an existing object" then find out what object has that name.

select owner,object_type
from all_objects where 
object_name='<NAME_OF_OBJECT>'

Upvotes: 0

Manish Samariya
Manish Samariya

Reputation: 1

You should rename your primary key.

CREATE TABLE User(
    user_id    NUMBER(38, 0)    NOT NULL,
    user_name  VARCHAR2(25)     NOT NULL,
    CONSTRAINT PK_User_id PRIMARY KEY (user_id)
);

Upvotes: 0

Ludovic Feltz
Ludovic Feltz

Reputation: 11916

It is because your primary key name is already taken. You should rename it:

ALTER INDEX "PK_User" RENAME TO "PK_Account_User";

You can check which names are already taken with the following command:

SELECT * FROM USER_INDEXES WHERE TABLE_NAME='<table_name>';

Upvotes: 5

Kris Rice
Kris Rice

Reputation: 3410

user is a reserved word.

KLRICE@xe🍻🍺 >CREATE TABLE User(
  2      user_id    NUMBER(38, 0)    NOT NULL,
  3      user_name  VARCHAR2(25)     NOT NULL,
  4      CONSTRAINT PK_User PRIMARY KEY (user_id)
  5  );

Error starting at line : 1 in command -
CREATE TABLE User(
    user_id    NUMBER(38, 0)    NOT NULL,
    user_name  VARCHAR2(25)     NOT NULL,
    CONSTRAINT PK_User PRIMARY KEY (user_id)
)
Error report -
ORA-00903: invalid table name

Use a prefix or another word

1  CREATE TABLE my_user (
  2      user_id    NUMBER(38, 0)    NOT NULL,
  3      user_name  VARCHAR2(25)     NOT NULL,
  4      CONSTRAINT PK_User PRIMARY KEY (user_id)
  5* );
KLRICE@xe🍻🍺 >/

Table MY_USER created.

KLRICE@xe🍻🍺 >

Upvotes: 0

Related Questions