Home » SQL & PL/SQL » SQL & PL/SQL » index not getting dropped
index not getting dropped [message #224098] Tue, 13 March 2007 01:45 Go to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Hi,
After dropping the unique key constraint on a table the unique index is still there for that column.I haven't created it explicitly.
What may be the reason?
Any help appreciated.
Thanks,
Karisma
Re: index not getting dropped [message #224113 is a reply to message #224098] Tue, 13 March 2007 02:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you created the index and the key separately, disabling/dropping the key does not drop the index. It is also possible that you are using a GUI to drop the key that includes the clause to preserve indexes.

Ross Leishman
Re: index not getting dropped [message #224118 is a reply to message #224113] Tue, 13 March 2007 02:37 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
no in my create table statement only 'constraint con_name unique key(col_name)' clause is there and i haven't created any index on that column.I am disabling/dropping the constraint from sql*plus environment.
Thanks,
karisma
Re: index not getting dropped [message #224123 is a reply to message #224118] Tue, 13 March 2007 03:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's very strange. Why don't you put together a test script with the CREATE TABLE statement, DROP TABLE statement, and then a SELECT FROM user_indexes that demonstrates the existence of the index. Then we can see whether it happens the same on our databases.

Ross Leishman
Re: index not getting dropped [message #224138 is a reply to message #224098] Tue, 13 March 2007 04:06 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member

I have done like this.


CREATE TABLE ww_Patient (
Patient_ID NUMBER(20) NOT NULL,
Patient_Cd VARCHAR2(20) NOT NULL,
Customer_ID NUMBER(20) NOT NULL,
Patient_Name VARCHAR2(50) NOT NULL,
Tag_ID VARCHAR2(20) ,
Species_ID NUMBER(20) ,
Breed_ID NUMBER(20) NOT NULL,
Color_CD VARCHAR2(50) NOT NULL,
Sex_CD VARCHAR2(50) NOT NULL,
Birth_Day DATE ,
Weight NUMBER(15,2) ,
Weight_Unit_Of_Measure_Cd VARCHAR2(50) ,
Weight_Dt DATE ,
Deceased_Flag CHAR(1) ,
Deceased_Dt DATE ,
Last_Doctor_ID NUMBER(10) ,
Registered_Flag CHAR(1) ,
Eye_Exam_Flag CHAR(1) ,
Reminder_Document_ID NUMBER(10) ,
Geriatric_Years NUMBER(2) ,
Indoor_Pet_Flag CHAR(1) ,
Stud_Flag CHAR(1) ,
Neuter_Flag CHAR(1) ,
Last_Electronic_Chip_ID VARCHAR2(20) ,
Last_Tattoo_ID VARCHAR2(20) ,
Serial_ID VARCHAR2(20) ,
Converted_Dt DATE ,
Merger_Cd VARCHAR2(10) ,
Merger_Hospital_Name VARCHAR2(50) ,
Mixed_Breed_Flag CHAR(1) ,
OFA_Certified_Flag CHAR(1) ,
Cage_ID NUMBER(10) ,
deletion_ind char(1) default '0',
Created_Dt DATE ,
Created_By Number(20) ,
Modified_Dt DATE ,
Modified_By Number(20) ,
Message_No NUMBER(10) ,
active_flag CHAR(1) ,
merged_flag char(1),
original_pat_id number(20),
CONSTRAINT PK_ww_Patient PRIMARY KEY (Patient_ID),
CONSTRAINT FK_ww_Customer_ww_Patient FOREIGN KEY (Customer_ID) REFERENCES ww_Customer,
CONSTRAINT UK_ww_Patient UNIQUE (Patient_Cd ),
constraint FK_BREED_PATIENT_BREED_ID FOREIGN KEY (BREED_ID) REFERENCES WW_BREED (BREED_ID),
constraint FK_SPECIES_PATIENT_SPECIES_ID FOREIGN KEY (SPECIES_ID) REFERENCES WW_SPECIES (SPECIES_ID),
constraint fk_ww_pat_orig_pat_id foreign key (original_pat_id) references ww_patient(patient_id)
) TABLESPACE WOOFWARE_TS;



SQL> insert into ww_patient (patient_id, patient_cd, customer_id, patient_name, breed_id, species_id, color_cd, sex_cd) select seq_patient_id.nextval, patient_cd, customer_id, patient_name, breed_id, species_id, color_cd, sex_cd from ww_patient where patient_id=273010082;
insert into ww_patient (patient_id, patient_cd, customer_id, patient_name, breed_id, species_id, color_cd, sex_cd) select seq_patient_id.nextval, patient_cd, customer_id, patient_name, breed_id, species_id, color_cd, sex_cd from ww_patient where patient_id=273010082
*
ERROR at line 1:
ORA-00001: unique constraint (WW_ADMIN.UK_WW_PATIENT) violated


Elapsed: 00:00:00.03
SQL> select owner, constraint_name, status from all_constraints where table_name='WW_PATIENT';

OWNER CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ --------
WW_ADMIN UK_WW_PATIENT DISABLED
WW_ADMIN SYS_C0015155 ENABLED
WW_ADMIN SYS_C0015156 ENABLED
WW_ADMIN SYS_C0015157 ENABLED
WW_ADMIN SYS_C0015158 ENABLED
WW_ADMIN SYS_C0015159 ENABLED
WW_ADMIN SYS_C0015160 ENABLED
WW_ADMIN SYS_C0015161 ENABLED
WW_ADMIN PK_WW_PATIENT ENABLED
WW_ADMIN FK_SPECIES_PATIENT_SPECIES_ID ENABLED
WW_ADMIN FK_BREED_PATIENT_BREED_ID ENABLED
WW_ADMIN FK_WW_CUSTOMER_WW_PATIENT ENABLED
WW_ADMIN FK_WW_PAT_ORIG_PAT_ID ENABLED

13 rows selected.

Elapsed: 00:00:00.03
SQL> alter table ww_patient drop constraint UK_WW_PATIENT;

Table altered.

Elapsed: 00:00:00.17
SQL> select owner, constraint_name, status from all_constraints where table_name='WW_PATIENT';

OWNER CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ --------
WW_ADMIN SYS_C0015155 ENABLED
WW_ADMIN SYS_C0015156 ENABLED
WW_ADMIN SYS_C0015157 ENABLED
WW_ADMIN SYS_C0015158 ENABLED
WW_ADMIN SYS_C0015159 ENABLED
WW_ADMIN SYS_C0015160 ENABLED
WW_ADMIN SYS_C0015161 ENABLED
WW_ADMIN PK_WW_PATIENT ENABLED
WW_ADMIN FK_SPECIES_PATIENT_SPECIES_ID ENABLED
WW_ADMIN FK_BREED_PATIENT_BREED_ID ENABLED
WW_ADMIN FK_WW_CUSTOMER_WW_PATIENT ENABLED
WW_ADMIN FK_WW_PAT_ORIG_PAT_ID ENABLED

12 rows selected.

Elapsed: 00:00:00.09
SQL> insert into ww_patient (patient_id, patient_cd, customer_id, patient_name, breed_id, species_id, color_cd, sex_cd) select seq_patient_id.nextval, patient_cd, customer_id, patient_name, breed_id, species_id, color_cd, sex_cd from ww_patient where patient_id=273010082;
insert into ww_patient (patient_id, patient_cd, customer_id, patient_name, breed_id, species_id, color_cd, sex_cd) select seq_patient_id.nextval, patient_cd, customer_id, patient_name, breed_id, species_id, color_cd, sex_cd from ww_patient where patient_id=273010082
*
ERROR at line 1:
ORA-00001: unique constraint (WW_ADMIN.UK_WW_PATIENT) violated


Elapsed: 00:00:00.23
SQL> select count(*) from user_recyclebin;

COUNT(*)
----------
0

Elapsed: 00:00:00.17
SQL>




Thanks,
Karisma
Re: index not getting dropped [message #224186 is a reply to message #224138] Tue, 13 March 2007 06:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That is not a test script.

You have included the CREATE TABLE statement, but I don't see a "Table Created" feedback message from SQL*Plus, which means you just cut and pasted the CREATE TABLE into your test script. Also, you ran 2 INSERTs, but the feedback is not shown for the first one; more evidence of test-case tampering.

ie. We have no idea what has gone on before this.

Since the status of the constraint was disabled before you dropped it, I suspect that something happened that you haven't shown us.

If you want help, include a FULL TEST SCRIPT, from start to finish - no short cuts.

Ross Leishman
Re: index not getting dropped [message #224277 is a reply to message #224098] Tue, 13 March 2007 12:41 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
placing a unique constraint on a column explicatively generates a unique index. Otherwise how would the uniqueness be enforced without a full table scan.
Re: index not getting dropped [message #224366 is a reply to message #224098] Wed, 14 March 2007 01:56 Go to previous message
karismapanda
Messages: 58
Registered: January 2007
Member
Thanks all,its working now
Previous Topic: Help - Slow running Quey - (Sort Merge Join)
Next Topic: distinct and gropu by caluse
Goto Forum:
  


Current Time: Fri Dec 09 19:29:30 CST 2016

Total time taken to generate the page: 0.11905 seconds