DBAI_les13_rev1_1

Views:
 
Category: Education
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

Maintaining Data Integrity : 

Maintaining Data Integrity

Objectives : 

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 : 

Data Integrity Applicationcode Table Data Integrityconstraint Databasetrigger

Types of Constraints : 

Types of Constraints Constraint NOT NULL UNIQUEPRIMARY KEYFOREIGN KEY CHECK Description Specifies that a column cannot contain null values Designates a column or combination of columns as unique Designates a column or combination of columns as the table’s primary keyDesignates a column or combination of columns as the foreign key in a referential integrity constraintSpecifies a condition that each row of the table must satisfy

Constraint States : 

Constraint States ENABLENOVALIDATE Existing data New data DISABLENOVALIDATE DISABLEVALIDATE = = ENABLEVALIDATE

Constraint Checking : 

Constraint Checking DML statement Check nondeferred constraints COMMIT Check deferred constraints

Defining Constraints Immediate or Deferred : 

Defining Constraints Immediate 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 : 

Primary and Unique Key Enforcement Is an index available for use? Yes No No Yes Yes No Create nonunique index Create unique index Do not use index Use existing index Keyenabled? Constraintdeferrable? Constraint Deferrable? Is the index nonunique? Yes No/Yes No

Foreign Key Considerations : 

Foreign Key Considerations

Defining Constraints WhileCreating a Table : 

Defining Constraints WhileCreating a Table CREATE TABLE hr.employee(id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX STORAGE(INITIAL 100K NEXT 100K) TABLESPACE indx, last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER(7)) TABLESPACE users;

Guidelines for Defining Constraints : 

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 : 

Enabling Constraints No locks on table Primary and unique keys must use nonunique indexes ENABLE NOVALIDATE ALTER TABLE hr.departmentsENABLE NOVALIDATE CONSTRAINT dept_pk;

Enabling Constraints : 

Enabling Constraints Locks the table Can use unique or nonunique indexes Needs valid table data ENABLE VALIDATE ALTER TABLE hr.employeesENABLE VALIDATE CONSTRAINT emp_dept_fk;

Using the EXCEPTIONS Table : 

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 : 

Obtaining Constraint Information Obtain information about constraints by querying the following views: DBA_CONSTRAINTS DBA_CONS_COLUMNS

Summary : 

Summary In this lesson, you should have learned how to: Implement data integrity Use an appropriate strategy to create and maintain constraints Obtain information from the data dictionary

Practice 13 Overview : 

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

authorStream Live Help