Maintaining Data Integrity参考.ppt

Maintaining Data Integrity Objectives After completing this lesson, you should be able to do the following: Implement data integrity constraints Maintain integrity constraints Obtain constraint information from the data dictionary Data Integrity Types of Constraints Constraint States Constraint Checking DML statement Defining Constraints asImmediate or Deferred Use the SET CONSTRAINTS statement to make constraints either DEFERRED or IMMEDIATE The ALTER SESSION statement also has clauses to SET CONSTRAINTS to DEFERRED or IMMEDATE Primary and Unique Key Enforcement Foreign Key Considerations Defining Constraints WhileCreating a Table Guidelines for Defining Constraints Primary and unique constraints: Place indexes in a separate tablespace Use nonunique indexes if bulk loads are frequent Self-referencing foreign keys: Define or enable foreign keys after initial load Defer constraint checking Enabling Constraints No locks on table Primary and unique keys must use nonunique indexes Enabling Constraints Locks table Can use unique or nonunique indexes Needs valid table data Using the EXCEPTIONS Table Create the EXCEPTIONS table by running the utlexcpt1.sql script Execute the ALTER TABLE statement with EXCEPTIONS option Use subquery on EXCEPTIONS to locate rows with invalid data Rectify the errors Reexecute ALTER TABLE to enable the constraint. Obtaining Constraint Information Data Dictionary Views DBA_CONSTRAINTS DBA_CONS_COLUMNS Summary In this lesson, you should have learned how to: Implement data integrity Use an appropriate strategy for creating and maintaining constraints Obtain information from the data dictionary Practice 13 Overview This practice covers the following topics: Creating constraints Enabling unique constraints Creating an Exceptions table Identifying existing constraint violations in a table, correcting the errors and re-enabling the constraints Objectives After completing this lesson, you should be able to do the following: Manage passwords us