| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to drop unique index
that's not dropping a unique index, that's dropping a column!
to drop an index:
drop index <indexname>;
doesn't matter if it is unique or not.
If you are trying to drop a unique constraint, that's different.
first find the name of the unique constraint on that table"
select constraint_name from user_constraints where constraint_type='U';
will give you all the unique constraints on that table.
If there is more than one unique constraint, check in user_cons_columns to match the column to the constraint.
Then you can
alter table <tablename> drop constraint <constraintname>;
Rachel
>From: "CHAN Chor Ling Catherine (CSC)" <clchan_at_nie.edu.sg>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: How to drop unique index
>Date: Wed, 30 May 2001 00:50:25 -0800
>
>Hi Gurus,
>
>I tried to drop unique index using "ALTER TABLE table_name DROP UNIQUE
>(column_name);" unsuccessfully. How should I drop the unique index ? Please
>advise. Thanks.
>
>SQL> SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME='SPY_ADHOC_PAYMENT';
>
>INDEX_NAME TABLE_NAME COLUMN_NAME
>------------------------------ ------------------------------
>------------------
>U_SPYADH_2 SPY_ADHOC_PAYMENT STDNAME
>
>
>SQL> SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM USER_INDEXES
>WHERE TABLE_NAME='SPY_ADHOC_PAYMENT';
>
>INDEX_NAME TABLE_OWNER TABLE_NAME
>UNIQUENES
>------------------------ ------------------------------ ------------------
>--------
>U_SPYADH_2 SPY SPY_ADHOC_PAYMENT
>UNIQUE
>
>SQL> ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME);
>ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP UNIQUE (STDNAME)
> *
>ERROR at line 1:
>ORA-02442: Cannot drop nonexistent unique key
>
>SQL> ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2;
>ALTER TABLE SPY.SPY_ADHOC_PAYMENT DROP CONSTRAINT U_SPYADH_2
> *
>ERROR at line 1:
>ORA-02443: Cannot drop constraint - nonexistent constraint
>
>Regds,
>New Bee
> -----Original Message-----
> From: Manivannan.M [mailto:manivannan.m_at_tatainfotech.com]
> Sent: Wednesday, May 30, 2001 2:20 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: catrep.sql
>
> Hi
>
> It will be there under $ORACLE_HOME/rdbms/admin
>
> regards,
> Manivannan.M
>
>----------------------------------------------------------------------------
>--
>
>
>
>
> On Tue, 29 May 2001, Timajo, Joel - Equicom wrote:
>
> > hello oracle gurus!
> >
> > i just tried a default installation of oracle 8.0.6 to our
>hp-unix machine.
> > i followed step by step the procedures as stated in the
>oracle installation
> > guide. my problem is when it comes to the step (pp 4-2 of
>the installation
> > guide) that says run the catrep.sql. where can i find this
>sql script?
> >
> > thanks!
> > oracle newbie
> >
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Manivannan.M
> INET: manivannan.m_at_tatainfotech.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
>538-5051
> San Diego, California -- Public Internet access /
>Mailing Lists
>
>--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail
>message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
>and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
>You may
> also send the HELP command for other information (like
>subscribing).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: CHAN Chor Ling Catherine (CSC)
> INET: clchan_at_nie.edu.sg
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed May 30 2001 - 10:32:45 CDT
 |
 |