les13_管理约束.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 Data Integrity Types of Constraints Constraint States Constraint Checking Defining ConstraintsImmediate 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 IMMEDIATE. 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 the initial load. Defer constraint checking. Enabling Constraints No locks on table Primary and unique keys must use nonunique indexes Enabling Constraints Locks the table Can use unique or nonunique indexes Needs valid table data Renaming Constraints Using the EXCEPTIONS Table Create the EXCEPTIONS table by running the utlexpt1.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 Obtain information about constraints by querying the following views: DBA_CONSTRAINTS DBA_CONS_COLUMNS Summary In this lesson, you should have learned how to: Implement data integrity Use an appropriate strategy to create and maintain constraints Obtain constraint information 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 reenabling the constraints Data Integrity Data integrity means that data in a database adheres to business r