Books > Les13_rev2

13

Maintaining Data Integrity

Copyright © Oracle Corporation, 2002. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: • Implement data integrity constraints • Maintain integrity constraints • Obtain constraint information

13-2

Copyright © Oracle Corporation, 2002. All rights reserved.

Data Integrity Database trigger

Data

Integrity constraint

Application code

Table

13-3

Copyright © Oracle Corporation, 2002. All rights reserved.

13-4

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

Constraint States DISABLE NOVALIDATE

DISABLE VALIDATE

ENABLE NOVALIDATE

ENABLE VALIDATE

= = New data

13-6

Existing data

Copyright © Oracle Corporation, 2002. All rights reserved.

13-7

Copyright © Oracle Corporation, 2002. All rights reserved.

Constraint Checking

DML statement Check nondeferred constraints

COMMIT

Check deferred constraints

13-8

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

Primary and Unique Key Enforcement Key enabled?

Yes

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, 2002. 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, 2002. All rights reserved.

13-12

Copyright © Oracle Corporation, 2002. 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 100K NEXT 100K) TABLESPACE indx, last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER(7)) TABLESPACE users;

13-13

Copyright © Oracle Corporation, 2002. All rights reserved.

13-14

Copyright © Oracle Corporation, 2002. All rights reserved.

13-15

Copyright © Oracle Corporation, 2002. All rights reserved.

13-16

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

13-19

Copyright © Oracle Corporation, 2002. All rights reserved.

13-20

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

13-22

Copyright © Oracle Corporation, 2002. All rights reserved.

Renaming Constraints Use the following to rename a constraint: ALTER TABLE employees RENAME CONSTRAINT emp_dept_fk TO employees_dept_fk;

13-23

Copyright © Oracle Corporation, 2002. All rights reserved.

13-24

Copyright © Oracle Corporation, 2002. All rights reserved.

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.

13-25

Copyright © Oracle Corporation, 2002. All rights reserved.

13-26

Copyright © Oracle Corporation, 2002. All rights reserved.

13-27

Copyright © Oracle Corporation, 2002. All rights reserved.

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

13-28

Copyright © Oracle Corporation, 2002. All rights reserved.

13-29

Copyright © Oracle Corporation, 2002. All rights reserved.

13-30

Copyright © Oracle Corporation, 2002. 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 constraint information

13-31

Copyright © Oracle Corporation, 2002. 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-32

Copyright © Oracle Corporation, 2002. All rights reserved.

13-33

Copyright © Oracle Corporation, 2002. All rights reserved.

13-34

Copyright © Oracle Corporation, 2002. All rights reserved.