Home » SQL & PL/SQL » SQL & PL/SQL » unique constraint error..
unique constraint error.. [message #347183] Thu, 11 September 2008 00:36 Go to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Hi

When i trie to insert into table its showing unique constraint error with constarint name.. but if i try to drop the constarint then it is shaying can not drop no existing constraint.. i couldnt find that constraint in user_constraint or all constraint.. whay it is so and how can i drop that constraint?

rgds,
manju
Re: unique constraint error.. [message #347188 is a reply to message #347183] Thu, 11 September 2008 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us what you tried in a copy-paste from sqlplus.
That is clearer to us than you describing in words what happened
Re: unique constraint error.. [message #347198 is a reply to message #347188] Thu, 11 September 2008 01:00 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

tried for as below..
INSERT INTO sttw_cust_personal(customer_no,version_no) VALUES('0690126',11);

ORA-00001: unique constraint (FCCISR.UX04_STTW_CUST_PERSONAL) violated

then tried for
alter table sttw_cust_personal drop constraint UX04_STTW_CUST_PERSONAL;

ORA-02443: Cannot drop constraint  - nonexistent constraint

even in
 select * from all_constraints where constraint_name='UX04_STTW_CUST_PERSONAL';
no rows selected

why it is so.. where this constraint from??


[Mod-edit: Frank added code tags]

[Updated on: Thu, 11 September 2008 01:05] by Moderator

Report message to a moderator

Re: unique constraint error.. [message #347203 is a reply to message #347198] Thu, 11 September 2008 01:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show the results of a query on all_constraints where you filter on the table_name instead of on the constraint_name.
Also show a query on all_objects, showing owner and object_type for the object_name STTW_CUST_PERSONAL
Re: unique constraint error.. [message #347206 is a reply to message #347198] Thu, 11 September 2008 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why it is so.. where this constraint from??

Maybe it is not yours.
"show user"

Regards
Michel
Re: unique constraint error.. [message #347210 is a reply to message #347206] Thu, 11 September 2008 01:32 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Hi

i am in FCCISR user only.
SQL> show user
USER is "FCCISR"

select * from all_constraints where table_name=upper('sttw_cust_personal');
OWNER                          CONSTRAINT_NAME                C TABLE_NAME              
------------------------------ ------------------------------ - ------------------
FCCISR                         PK_STTW_CUST_PERSONAL          P STTW_CUST_PERSONAL
FCCISR                         CN01_STTW_CUST_PERSONAL        C STTW_CUST_PERSONAL
FCCISR                         CN02_STTW_CUST_PERSONAL        C STTW_CUST_PERSONAL

select * from all_objects where object_name='STTW_CUST_PERSONAL';

OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE      
------------------------------ ------------------------------ ------------------------------ ---------- -------------- --------------
PUBLIC                         STTW_CUST_PERSONAL                                                 93023                SYNONYM          
FCCISR                         STTW_CUST_PERSONAL                                                108663         108663 TABLE              


[Mod-edit: Frank added code tags again]

[Updated on: Thu, 11 September 2008 01:35] by Moderator

Report message to a moderator

Re: unique constraint error.. [message #347211 is a reply to message #347210] Thu, 11 September 2008 01:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Probably a unique index:
SQL> create table faq_tst(id number);

Table created.

SQL> create unique index my_index on faq_tst(id);

Index created.

SQL> insert into faq_tst values (1);

1 row created.

SQL> insert into faq_tst values (1);
insert into faq_tst values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.MY_INDEX) violated


SQL> select * from user_constraints where table_name = 'FAQ_TST';

no rows selected
Re: unique constraint error.. [message #347212 is a reply to message #347211] Thu, 11 September 2008 01:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Oh, and please add [code]-tags around your code next time. It improves readability, because it conserves the formatting of the text.
Re: unique constraint error.. [message #347213 is a reply to message #347211] Thu, 11 September 2008 01:43 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

How can I drop this index which is not showing?
rgds,
Manju
Re: unique constraint error.. [message #347214 is a reply to message #347213] Thu, 11 September 2008 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you mean 'it is not showing'?
Uniqueness is enforced by the index, not a constraint. Drop the index. (A query on all indexes would reveal the beast)

Before dropping the index though, consider if you really want to drop it or that you need the index (as non-unique) for performance purposes..
Re: unique constraint error.. [message #347222 is a reply to message #347214] Thu, 11 September 2008 02:07 Go to previous messageGo to next message
manns
Messages: 59
Registered: March 2006
Location: Bangalore
Member

Thanks, unique constraint is present.. How can i conver into no unique?
Re: unique constraint error.. [message #347230 is a reply to message #347222] Thu, 11 September 2008 02:25 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop and recreate the index.

Regards
Michel
Previous Topic: Inserting Data For a long time??
Next Topic: table in TEMP Tablespace
Goto Forum:
  


Current Time: Fri Dec 09 11:31:23 CST 2016

Total time taken to generate the page: 0.23774 seconds