Home » SQL & PL/SQL » SQL & PL/SQL » Error - trying to drop and add a primary key
Error - trying to drop and add a primary key [message #229827] Tue, 10 April 2007 02:36 Go to next message
smora
Messages: 59
Registered: May 2006
Member
Hi

I am trying to drop and add a primary key to an existing table, and am getting an error "ORA-00955: name is already used by an existing object" even after the drop executes successfully.

Any help is appreciated.
Thanks
SM


create table b_imps
(b_id number(32) not null,
s_id number(32) not null,
ti number(10),
di number(10),
tc number(10),
dc number(10),
constraint bi_pk primary key (b_id),
constraint bi_banner_fk foreign key (b_id) references bns(b_id)
);

SQL>alter table b_imps drop constraint bi_pk;
Table altered.

SQL>alter table b_imps add constraint bi_pk primary key (b_id, s_id);
alter table b_imps add constraint bi_pk primary key (b_id, s_id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL>commit;
Commit complete.

SQL>alter table b_imps add constraint bi_pk primary key (b_id, s_id);
alter table b_imps add constraint bi_pk primary key (b_id, s_id)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL>alter table b_imps drop constraint bi_pk;
alter table b_imps drop constraint bi_pk
ERROR at line 1:
ORA-02443: Cannot drop constraint - nonexistent constraint

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #229832 is a reply to message #229827] Tue, 10 April 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It seems someone else creates a new constraint with the same name.
I can't reproduce your test case:
SQL> create table b_imps
  2  (b_id number(32) not null,
  3  s_id number(32) not null,
  4  ti number(10),
  5  di number(10),
  6  tc number(10),
  7  dc number(10),
  8  constraint bi_pk primary key (b_id)
  9  );

Table created.

SQL> alter table b_imps drop constraint bi_pk;

Table altered.

SQL> alter table b_imps add constraint bi_pk primary key (b_id, s_id);

Table altered.

Post your full Oracle version.

Regards
Michel

Report message to a moderator

Re: can i find nth row in oracle for particular table [message #229833 is a reply to message #229827] Tue, 10 April 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
can u tell me how to find nth row of particular table in oracle

Create a primary key.

Regards
Michel

Report message to a moderator

Re: can i find nth row in oracle for particular table [message #229835 is a reply to message #229827] Tue, 10 April 2007 02:44 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
HI,
can u tell me how to find nth row of particular table in oracle.

YOu should create new topic for every new question.
and about your question you should search on FORMS you will get alot of answers.

Regards
Taj

Report message to a moderator

Re: can i find nth row in oracle for particular table [message #229836 is a reply to message #229833] Tue, 10 April 2007 02:45 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Hi,
Create a primary key. ?
Not understand.


Regards
Taj

Report message to a moderator

Re: can i find nth row in oracle for particular table [message #229852 is a reply to message #229836] Tue, 10 April 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Mohammad,

Misplaced or absurd question
Leads to
Misplaced or absurd answer

Regards
Michel

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230287 is a reply to message #229827] Wed, 11 April 2007 08:00 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Going back to the original topic: Any ideas why the name shows up as still in use after it has been dropped?

SQL>desc b_imps
Name Null? Type
--------------------------------- -------- --------------
B_ID NOT NULL NUMBER(32)
TOTAL_IMPS NUMBER(10)
DAILY_IMPS NUMBER(10)
TOTAL_CTS NUMBER(10)
DAILY_CTS NUMBER(10)
S_ID NOT NULL NUMBER(32)

SQL>select constraint_name from user_constraints where table_name = 'B_IMPS';

CONSTRAINT_NAME
------------------------------
SYS_C0014338
BI_PK
BI_BANNER_FK
SYS_C0014019

Elapsed: 00:00:01.95
SQL>alter table b_imps drop constraint bi_pk;

Table altered.

Elapsed: 00:00:01.57
SQL>commit;

Commit complete.

Elapsed: 00:00:01.54
SQL>select constraint_name from user_constraints where table_name = 'B_IMPS';

CONSTRAINT_NAME
------------------------------
SYS_C0014338
BI_BANNER_FK
SYS_C0014019

Elapsed: 00:00:02.10
SQL>alter table b_imps add constraint bi_pk primary key (B_ID, S_ID);
alter table b_imps add constraint bi_pk primary key (B_ID, S_ID)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


Elapsed: 00:00:01.95
SQL>

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230291 is a reply to message #230287] Wed, 11 April 2007 08:05 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
 
select * from user_objects where object_name='BI_PK';

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230297 is a reply to message #229827] Wed, 11 April 2007 08:21 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
SQL>select * from user_objects where object_name='BI_PK';

OBJECT_NAME
----------------------------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED
------------------------------ ---------- -------------- ------------------- -------------------
LAST_DDL_TIME TIMESTAMP STATUS T G S
------------------- ------------------- ------- - - -
BI_PK
64348 64348 INDEX 2007-04-10 08:13:32
2007-04-10 08:13:32 2007-04-10:08:13:32 VALID N N N

Thanks Joy,

I dropped the primary key, so what object is this? How do i drop it?

SM

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230302 is a reply to message #230297] Wed, 11 April 2007 08:42 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Aha, ok, you created the primary key initially by using an already existing index. You'll have to drop this index or just create the primary key with a different name.

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230471 is a reply to message #229827] Thu, 12 April 2007 01:41 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Joy

I dropped the index, and am now able to create the primary key with the same name. Question: I did not explicitly create an index, this is the command i used to create the table and the primary key.

create table b_imps
(b_id number(32) not null,
total_imps number(10),
daily_imps number(10),
total_cts number(10),
daily_cts number(10),
constraint bi_pk primary key (b_id),
constraint bi_banner_fk foreign key (b_id) references bnrs(b_id)
);

So how did that index get created?

Thanks
SM

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230477 is a reply to message #230471] Thu, 12 April 2007 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post exactly the test like I did and let us see what happens.

Regards
Michel

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230488 is a reply to message #229827] Thu, 12 April 2007 02:10 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
I did the test, and I cannot reporoduce the error. I know i did not explicitly create the index, very strange...

Thank you all for your help.
SM


1 create table b_imps
2 (b_id number(32) not null,
3 s_id number(32) not null,
4 ti number(10),
5 di number(10),
6 tc number(10),
7 dc number(10),
8 constraint bi_pk2 primary key (b_id)
9* )
SQL>/

Table created.

Elapsed: 00:00:01.59
SQL>select constraint_name from user_constraints where table_name = 'B_IMPS';

CONSTRAINT_NAME
------------------------------
SYS_C0014380
SYS_C0014381
BI_PK2

Elapsed: 00:00:01.87
SQL>alter table b_imps drop constraint bi_pk2;

Table altered.

Elapsed: 00:00:01.92
SQL>select constraint_name from user_constraints where table_name = 'B_IMPS';

CONSTRAINT_NAME
------------------------------
SYS_C0014380
SYS_C0014381

Elapsed: 00:00:02.21
SQL>ed
Wrote file afiedt.buf

1* select * from user_objects where object_name = 'BI_PK2'
SQL>/

no rows selected

Elapsed: 00:00:01.57
SQL>alter table b_imps add constraint bi_pk2 primary key (b_id, s_id);

Table altered.

Elapsed: 00:00:01.85
SQL>

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230610 is a reply to message #229827] Thu, 12 April 2007 05:41 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
However, when I recreate the primary key, the index is recreated as well.

SQL>alter table b_imps drop constraint bi_pk;

Table altered.

Elapsed: 00:00:01.60
SQL>drop index BI_PK;

Index dropped.

Elapsed: 00:00:01.96
SQL>commit;

Commit complete.

Elapsed: 00:00:01.71
SQL>alter table b_imps add constraint bi_pk primary key (b_id, s_id);

Table altered.

Elapsed: 00:00:01.70

1* select constraint_name from user_constraints where table_name = 'B_IMPS'
SQL>/

CONSTRAINT_NAME
------------------------------
BI_PK
BI_BANNER_FK
SYS_C0014761
SYS_C0014499

SQL>select * from user_objects where object_name = 'BI_PK';

OBJECT_NAME
----------------------------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED
------------------------------ ---------- -------------- ------------------- -------------------
LAST_DDL_TIME TIMESTAMP STATUS T G S
------------------- ------------------- ------- - - -
BI_PK
64793 64793 INDEX 2007-04-12 06:33:03
2007-04-12 06:33:03 2007-04-12:06:33:03 VALID N N N

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230615 is a reply to message #230610] Thu, 12 April 2007 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is the expected behaviour.

Regards
Michel

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230620 is a reply to message #229827] Thu, 12 April 2007 06:21 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Ok, so the problem was that I was trying to drop and recreate the primary key without explicitly dropping the implicitly created index of the same name as the primary key first.

Thanks all
SM

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230629 is a reply to message #230620] Thu, 12 April 2007 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No.
The index is dropped at the same time the primary key is if it is created with the primary key.
If the primary key is created upon an existing index, dropping the primary key does not drop the index.

Regards
Michel

Report message to a moderator

Re: Error - trying to drop and add a primary key [message #230655 is a reply to message #230620] Thu, 12 April 2007 08:52 Go to previous message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
SQL> create table t (id integer);

Table created.

SQL> alter table t add constraint t_pk primary key (id);

Table altered.

SQL> select index_name from user_indexes where table_name='T';
INDEX_NAME
------------------------------
T_PK

1 row selected.

SQL> alter table t drop primary key;

Table altered.

SQL> select index_name from user_indexes where table_name='T';

no rows selected

SQL> create unique index t_i on t (id);

Index created.

SQL> select index_name from user_indexes where table_name='T';
INDEX_NAME
------------------------------
T_I

1 row selected.

SQL> alter table t add constraint t_pk primary key (id);

Table altered.

SQL> select index_name from user_indexes where table_name='T';
INDEX_NAME
------------------------------
T_I

1 row selected.

SQL> alter table t drop primary key;

Table altered.

SQL> select index_name from user_indexes where table_name='T';
INDEX_NAME
------------------------------
T_I

1 row selected.

Regards
Michel

Report message to a moderator

Previous Topic: a difference of 0.1
Next Topic: Query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Aug 24 15:43:22 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.