ENABLING AND DISABLING CONSTRAINTS

ALTER TABLE can be used to enable and disable constraints without dropping constraints.When a constraint is disabled, Oracle does not enforce the rule defined by constraint. This may be useful when you need to insert a large number of rows and does not want Oracle to apply constraints as it takes a lot of time.
To disable PRIMARY KEY on SUBJECTS table:

ALTER TABLE courses DISABLE PRIMARY KEY;

Or you can drop any constraint by giving its name as follows:

alter table courses disable constraint courses_cat_chk;

If the constraint has depending constraints then you must use CASCADE clause to disable dependent constraints.

If the constraint has depending constraints then you must use CASCADE clause to disable dependent constraints.
You can enable a disabled constraints using ENABLE clause as follows:
alter table courses disable constraint courses_cat_chk;
Note: You can find out status of a constraint by using STATUS column of USER_CONSTRAINTS data dictionary view.

Dropping a table :To drop a table, use DDL command DROP TABLE. It removes the data as well as structure of the table The following is the syntax of DROP TABLE command.

DROP TABLE tablename [CASCADE CONSTRAINTS];

CASCADE CONSTRAINTS :clause is used to drop constraints that refer to primary and unique keys in the dropped table. If you do not give this clause and if referential integrity (references constraint) constraints exists then Oracle displays then Oracle displays error and doesn’t drop the table.

The following command will drop FACULTY table.

DROP TABLE faculty;

Note: When table is dropped, Views, and Synonyms based on the table will be made invalid, though they remain in the system. Also note, dropping a table cannot be undone.

Manipulating data :

As we have seen in the first chapter, SQL commands are divided into DML commands and DDL commands. DDL commands such as CREATE TABLE, ALTER TABLE, and DROP TABLE are dealing with definition of the table or structure of the table.
DML commands such as INSERT, DELETE and UPDATE are used to manipulate the data of the table. We have already seen how to use INSERT command to insert rows into table. Now let us see two other DML commands.

Updating rows using UPDATE command
UPDATE command is used to modify existing data in the rows. The following is the syntax of UPDATE command.
UPDATE table SET column = {expression | subquery}
[, column = {expression | subquery}] ...
[WHERE condition];
If WHERE clause is not given then all the rows of the table will be effected by the
change. In fact, it is more often the result of an error than intentional.
The following command will change course fee of ASP to 6000.
Update courses set fee = 6000
Where ccode = ‘asp’;
It is also possible to change more than one column at a time as follows:
update courses set fee = 6000, duration=30
where ccdoe = ‘asp’;
Note: We will discuss how to use subquery in UPDATE command later in this book.