Database Reference
In-Depth Information
We can also achieve the same result using the following
statement.
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9) UNIQUE,
loc VARCHAR2(10) );
The difference between this statement and the one mentioned
above, we haven't used the CONSTRAINT clause. So what is
the benefit of using this clause? The benefit is that we can
disable/enable the constraint using the ALTER TABLE
statement if the constraint name is created e.g.
Example:
ALTER TABLE dept
DROP CONSTRAINT unq_dname;
This statement will drop the constraint only from the table
altogether. But if you would like to disable the constraint
momentarily it's better to use the DISABLE clause instead of
DROP it.
Example:
ALTER TABLE dept
DISABLE CONSTRAINT unq_dname;
Once disabled this constraint will not be checked each and every
time whenever there is a new insertion or update of record. Now
the next question will popup in your mind, why there is a need to
disable a constraint? One of the application of disabling a
constraint comes at times when we do data migration from one
oracle system to another (or in other words bulk data loading
into tables using SQL*Loader), we will learn this later in this
topic. If we are sure that this data is already checked for
uniqueness as its coming from table already having UNIQUE
constraint on the column so there is no need for Oracle to
Search WWH ::
Custom Search