13 Maintaining Data Integrity Copyright Oracle Corporation 2001

Objectives After completing this lesson, you should be able to do the following: •

Data Integrity Database trigger Data Integrity constraint Application code Table 13 -3 Copyright ©

Types of Constraints 13 -5 Constraint Description NOT NULL Specifies that a column cannot

Constraint States DISABLE NOVALIDATE DISABLE VALIDATE ENABLE NOVALIDATE ENABLE VALIDATE = = New data

Constraint Checking DML statement Check nondeferred constraints COMMIT Check deferred constraints 13 -8 Copyright

Defining Constraints Immediate or Deferred • Use the SET CONSTRAINTS statement to make constraints

Primary and Unique Key Enforcement Yes Key enabled? Is an index available for use?

Foreign Key Considerations Desired Action Appropriate Solution Drop parent table Cascade constraints Truncate parent

Defining Constraints While Creating a Table CREATE TABLE hr. employee( id NUMBER(7) CONSTRAINT employee_id_pk

Guidelines for Defining Constraints • Primary and unique constraints: – Place indexes in a

Enabling Constraints ENABLE NOVALIDATE • No locks on table • Primary and unique keys

Enabling Constraints ENABLE VALIDATE • Locks the table • Can use unique or nonunique

Using the EXCEPTIONS Table • Create the EXCEPTIONS table by running the utlexcpt 1.

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

Summary In this lesson, you should have learned how to: • Implement data integrity

Practice 13 Overview This practice covers the following topics: • Creating constraints • Enabling

Slides: 17

Download presentation

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 13 -2 Copyright © Oracle Corporation, 2001. All rights reserved.

Types of Constraints 13 -5 Constraint Description NOT NULL Specifies that a column cannot contain null values UNIQUE Designates a column or combination of columns as unique PRIMARY KEY Designates a column or combination of columns as the table’s primary key FOREIGN KEY Designates a column or combination of columns as the foreign key in a referential integrity constraint CHECK Specifies a condition that each row of the table must satisfy Copyright © Oracle Corporation, 2001. All rights reserved.

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. 13 -9 Copyright © Oracle Corporation, 2001. All rights reserved.

Primary and Unique Key Enforcement Yes Key enabled? Is an index available for use? Yes No Constraint deferrable? No Do not use index 13 -10 Yes Constraint Deferrable? Is the index nonunique? No Use existing index No Create unique index Yes Create nonunique index Copyright © Oracle Corporation, 2001. All rights reserved. No/Yes

Foreign Key Considerations Desired Action Appropriate Solution Drop parent table Cascade constraints Truncate parent table Disable or drop foreign key Drop tablespace containing parent table Use the CASCADE CONSTRAINTS clause Perform DML on child table Ensure that the tablespace containing the parent key is online 13 -11 Copyright © Oracle Corporation, 2001. All rights reserved.

Defining Constraints While Creating a Table CREATE TABLE hr. employee( id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX STORAGE(INITIAL 100 K NEXT 100 K) TABLESPACE indx, last_name VARCHAR 2(25) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER(7)) TABLESPACE users; 13 -13 Copyright © Oracle Corporation, 2001. All rights reserved.

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. 13 -17 Copyright © Oracle Corporation, 2001. All rights reserved.

Enabling Constraints ENABLE NOVALIDATE • No locks on table • Primary and unique keys must use nonunique indexes ALTER TABLE hr. departments ENABLE NOVALIDATE CONSTRAINT dept_pk; 13 -18 Copyright © Oracle Corporation, 2001. All rights reserved.

Enabling Constraints ENABLE VALIDATE • Locks the table • Can use unique or nonunique indexes • Needs valid table data ALTER TABLE hr. employees ENABLE VALIDATE CONSTRAINT emp_dept_fk; 13 -21 Copyright © Oracle Corporation, 2001. All rights reserved.

Using the EXCEPTIONS Table • Create the EXCEPTIONS table by running the utlexcpt 1. 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. 13 -23 Copyright © Oracle Corporation, 2001. All rights reserved.

Obtaining Constraint Information Obtain information about constraints by querying the following views: • DBA_CONSTRAINTS • DBA_CONS_COLUMNS 13 -26 Copyright © Oracle Corporation, 2001. All rights reserved.

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 13 -29 Copyright © Oracle Corporation, 2001. All rights reserved.

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 13 -30 Copyright © Oracle Corporation, 2001. All rights reserved.