DROPPING A CONSTRAINTS

Used to drop the constraints defined on the table.
To drop a constraint, the name of the constraint is required. You may Use  USER_CONSTRAINTS data dictionary view to get the list of constraints.

alter table courses drop constraint courses_cat_chk;
CASCADE Option :

You cannot drop a UNIQUE or PRIMARY KEY constraint that is part of a referential integrity constraint without also dropping the corresponding foreign key. To drop PRIMARY KEY or UNIQUE constraint along with REFERENCES constraint use CASCADE option.

To drop PRIMARY KEY constraint of STUDENTS along with related constraint, do the following.

alter table courses
drop primary key cascade;

Note: You can get information about all the constraint using USER_CONSTRAINTS data dictionary view.

Dropping a column :

For the first time Oracle8i has provided a command to drop a column from the table. Till Oracle8, dropping a column is very lengthy task as there was no direct way to drop a column.
Oracle8i has provided a new option with ALTER TABLE command to drop a column – DROP COLUMN.
Actual you have two options when you want to drop a column.
Either you can drop unwanted column straight away. All the data related to the column
being dropped will be removed immediately.
Or you can mark the column for deletion and delete the column at a later stage. Since the column is marked for dropping, it is considered to be dropped. But the data of the column will remain until the column is physically removed.

The second options is especially useful considering the fact that dropping of a column does take a lot of time and if it is done when the load on the system is high then it will severely effect performance of the system. So you can mark a column for dropping and then drop the column when load on the system is low.
To drop column CAT of COURSES table, enter the following:

alter table courses drop column cat;

If column being dropped is either a PRIMARY KEY or UNIQUE key that is referenced by a foreign key, then it is not possible to drop the column. But it is possible if CASCADE CONSTRAINTS option is used. CASCADE CONSTRAINTS option drops all constraints that depend on the column being dropped.
To drop column FCODE column of FACULTY table along with all depending constraints, enter:
alter table faculty drop column fcode cascade constraints;

Note: When you drop a UNIQUE or PRIMARY KEY column then Oracle automatically drops the index that it creates to enforce uniqueness.

SET UNUSED option of ALTER TABLE command is used to mark a column for dropping. But the column is not physically removed. However, the column is treated as deleted. Once a column is marked as UNUSED then it cannot be accessed.
The following example marks column CAT or COURSES table as unused.

alter table courses set unused column cat;

Columns that are marked for deletion can be physically deleted using DROP UNUSED COLUMNS option of ALTER TABLE command as follows:
alter table courses drop unused columns;

Note: We can view the number of columns that are marked for deletion using USER_UNUSED_COL_TABS data dictionary view. USER_TAB_COLUMNS gives information about existing columns of a table.

Note: Until a column is physically dropped from the table it is counted as a column of the table and counted towards the absolute limit of 1000 columns per table.