Answers to some of the questions about indexes:

The user_cons_columns table allows you to see constraint names and see what columns the constraint is on.

How to see the indexes:

SQL CODE:

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'ORDERZ';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
CUSTID_FK                      CUSTID
ORDNO_PK                       ORDNO

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'ORDLINE';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
ITEMNO_FK                      ITEMNO
ORDNO_FK                       ORDNO
ORDNO_ITEMNO_PK                ORDNO
ORDNO_ITEMNO_PK                ITEMNO

Other uses of Drop:

You can put the following clauses on the ALTER TABLE command to deal with problematic indexes that you want to drop.

DROP PRIMARY KEY name of primary key;
DROP CONSTRAINT name of constraint;
DROP FOREIGN KEY name of foreign key;

You can disable a constraint by issuing the ALTER TABLE command with the DISABLE CONSTRAINT clause. If you add the CASCADE option you can disable dependent constraints. You can then active them again with the ENABLE CONSTRAINT clause.

You can also use the CASCADE clause with the DROP to drop a primary key and all associated links.

In the examples below, I am creating two tables: pay2x and dept2x. The primary key of pay2x is paycode and the primary key of dept2x is deptno. The deptno field on pay2x is linked to the deptno primary key on dept 2x as a foreign key.

First I created the primary key on pay2x. Notice there are no keys on dept2x as this point.

SQL CODE:

SQL> alter table pay2x 
     add constraint paycode_pk primary key(paycode);

Table altered.

SQL> desc pay2x;
 Name                            Null?    Type
 ------------------------------- -------- ----
 PAYCODE                         NOT NULL NUMBER(2)
 JOBNAME                                  VARCHAR2(15)
 DEPTNO                                   VARCHAR2(2)

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'PAY2X';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
PAYCODE_PK                     PAYCODE

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'DEPT2X';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
DEPTNO2X_PK                    DEPTNO

I then went in and dropped the primary key and checked to see that it no longer existed. Then I immediately put it back. I also added a primary key to dept2x (the code for that is not shown) and checked to see that it was there.

SQL CODE:

SQL> alter table pay2x 
  2  drop primary key;

Table altered.

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'PAY2X';

no rows selected

SQL> alter table pay2x
  2  add constraint paycode2x_pk primary key(paycode);

Table altered.

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'DEPT2X';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
DEPTNO2X_PK                    DEPTNO

SQL CODE:

In this example, I am adding the foreign key to go from the deptno in pay2x to the deptno in dept2x.

SQL CODE:

SQL> alter table pay2x
  2  add constraint deptno2x_fk foreign key (deptno) references dept2x;

Table altered.

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'PAY2X';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
DEPTNO2X_FK                    DEPTNO
PAYCODE2X_PK                   PAYCODE

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'DEPT2X';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
DEPTNO2X_PK                    DEPTNO

Finally, I droped the primary key in dept2x which is deptno and did it with the cascade clause which means that the foreign key will be dropped to. I checked it and it worked!

SQL CODE:

SQL> ALTER TABLE DEPT2X
      2  DROP PRIMARY KEY CASCADE;

Table altered.

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'DEPT2X';

no rows selected

SQL> select constraint_name, column_name from user_cons_columns where table_name = 'PAY2X';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ------------------------------
PAYCODE2X_PK                   PAYCODE