Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 7: Constraints
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 its 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. If we are
already sure that this data is checked for uniqueness as its coming from
table already having UNIQUE constraint on the column under discussion so
there is no need for Oracle to perform this step while uploading bulk
records and in this way we can save time of bulk data transfer.
Once done we can enable the constraint using the following statement and
this will bring the table back to its original state. A constraint that
is currently disabled can be enabled in one of the two ways: enable
NOVALIDATE or enable VALIDATE. The former does not check existing data
for constraint violation.
ALTER TABLE dept
ENABLE VALIDATE CONSTRAINT unq_dname;
Or,
ALTER TABLE dept
ENABLE NOVALIDATE CONSTRAINT unq_dname;
On the other hand, CHECK constraint is called custom constraint. You
have given the liberty to create or define the constraint according to
your needs.
Example:
CREATE TABLE suppliers
( supplier_id numeric(4),
supplier_name varchar2(50),
CONSTRAINT check_supplier_id
CHECK (supplier_id BETWEEN 100 and 9999)
);
Chapter 7: Constraints
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: