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