Guidelines for Defining Constraints Enabling Constraints ENABLE NOVALIDATE No

Guidelines for defining constraints enabling

This preview shows page 39 - 47 out of 110 pages.

Guidelines for Defining Constraints
Background image
Enabling Constraints ENABLE NOVALIDATE No locks on table Primary and unique keys must use non-unique indexes ALTER TABLE hr.departments ENABLE NOVALIDATE CONSTRATINT dept_pk;
Background image
A constraint that is currently disabled can be enabled in one of the two ways: Enable NOVALIDATE For PRIMARY KEY and UNIQUE constraints which have an existing index, enabling a constraint NOVALIDATE is much faster than enabling a constraint VALIDATE because existing data is not checked for constraint violation if the constraint is deferrable. If this option is used for enabling a constraint, no locks are required on the table. This method is appropriate where there is a lot of DML activity on a table, as in the case of an OLTP environment. Enabling Constraints
Background image
The following command can be used to enable a constraint ENABLE NOVALIDATE: ALTER TABLE [ schema. ] table ENABLE NOVALIDATE { CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column | , column | . . . ) } { USING INDEX index_clause } Enabling Constraints
Background image
Restrictions The USING INDEX clause is applicable only for primary key or unique constraints that were created as deferrable, and when one of the following is true: The constraints were created disabled The constraints were disabled and the index was dropped However, if the index needs to be created, using this method of enabling a constraint does not offer any significant venefit over ENABLE VALIDATE because the database server locks the table to build the index. Enabling Constraints
Background image
ENABLE VALIDATE Locks table Can use unique or nonunique indexes Needs valid table data ALTER TABLE hr.employees ENABLE VALIDATE CONSTRATINT emp_dept_fk; Enabling Constraints
Background image
Enabling a constraint VALIDATE checks existing data for constraint violation. This is the default when a constraint is enabled. If executed when the constraint is disabled, it has the following effects: The tables is locked and changes to the table are prevented until validation of existing data is complete. The database server creates an index if one does not exist on the index columns. It creates a unique index while enabling a primary key or unique constraint that is non-deferrable. A non-unique index is built for a deferrable primary key or a unique constraint. If this command is executed when a constraint is enforced, it does not require any table locks during validation. The enforced constraint guarantees that no violations are introduced during validation. This has the following advantages: All constraints are enabled concurrently. Each constraint is internally parallelized. Concurrent activity on the table is permitted Enabling Constraints
Background image
The following command is used to enable a constraint ENABLE VALIDATE: ALTER TABLE [ schema. ] table ENABLE [ VALIDATE ] { CONSTRAINT constraint | PRIMARY KEY | unique ( column [ , column ] . . . ) } [ USING INDEX index_clause ] [ EXCEPTIONS INTO [ schema. ] table ] The VALIDATE option is the default and does not need to be specified when enabling a constraint that is disabled.
Background image

Want to read all 110 pages?

Image of page 47

Want to read all 110 pages?

You've reached the end of your free preview.

Want to read all 110 pages?

  • Spring '15
  • dawan
  • Foreign key, database server, Constraint

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture