1、維護數(shù)據(jù)的完整性,目標,學完這個課程，你應該可以掌握你下內(nèi)容: 實現(xiàn)數(shù)據(jù)完整性約束 維護數(shù)據(jù)完整性約束 從數(shù)據(jù)字典中獲得完整性約束信息,Data Integrity,Applicationcode,Table,Data,Integrityconstraint,Databasetrigger,Types of Constraints,Constraint NOT NULL UNIQUEPRIMARY KEYFOREIGN KEY CHECK,Description Specifies that a column cannot contain null values Designates a co

2、lumn or combination of columns as unique Designates a column or combination of columns as the tables 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,ENABLE

3、NOVALIDATE,Existing data,New data,DISABLENOVALIDATE,DISABLEVALIDATE,=,=,ENABLEVALIDATE,Constraint Checking,DML statement,Check nondeferred constraints,COMMIT,Check deferred constraints,Defining Constraints Immediate or Deferred,Use the SET CONSTRAINTS statement to make constraints either DEFERRED or

4、 IMMEDIATE. The ALTER SESSION statement also has clauses to SET CONSTRAINTS to DEFERRED or IMMEDIATE.,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

5、?,Constraint Deferrable?,Is the index nonunique?,Yes,No/Yes,No,Foreign Key Considerations,Defining Constraints WhileCreating a Table,CREATE TABLE hr.employee(id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEYDEFERRABLE USING INDEX STORAGE(INITIAL 100K NEXT 100K) TABLESPACE indx, last_name VARCHAR2(2

6、5) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER(7) TABLESPACE users;,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

7、 after the initial load. Defer constraint checking.,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,Locks the table Can use unique or nonunique indexes Nee

8、ds valid table data,ENABLE VALIDATE,ALTER TABLE hr.employeesENABLE VALIDATE CONSTRAINT emp_dept_fk;,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 inv

9、alid 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 information from the data dictionary,Practice 13 Overview,This practice covers the following topics: Creating constraints Enabling uniqu