《maintaining data integrity》由会员分享，可在线阅读，更多相关《maintaining data integrity（55页珍藏版）》请在金锄头文库上搜索。
1、13Maintaining Data Integrity13-2ObjectivesAfter completing this lesson, you should be able to do the following: Implement data integrity constraints Maintain integrity constraints Obtain constraint information from the data dictionary13-3Data IntegrityApplication codeTableDataIntegrity constraintDat
2、abase trigger13-413-5Types of ConstraintsConstraintNOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECKDescriptionSpecifies that a column cannot contain null valuesDesignates a column or combination of columns as uniqueDesignates a column or combination of columns as the tables primary keyDesignates a column o
3、r combination of columns as the foreign key in a referential integrity constraintSpecifies a condition that each row of the table must satisfy13-6Constraint StatesENABLE NOVALIDATEENABLE VALIDATEExisting dataNew dataDISABLE NOVALIDATEDISABLE VALIDATE=13-713-8Constraint CheckingDML statementCheck non
4、deferred constraintsCOMMITCheck deferred constraints13-9Defining Constraints as Immediate or Deferred Use the SET CONSTRAINTS statement to make constraints either DEFERRED or IMMEDIATEThe ALTER SESSION statement also has clauses to SET CONSTRAINTS to DEFERRED or IMMEDATE13-10Primary and Unique Key E
5、nforcementIs an index available for use?YesNoNoYesYesNoCreate nonuniqueindexCreate uniqueindexDo not use indexUse existing indexKey enabled?Constraint deferrable?NoYesConstraint Deferrable?Is the index non-unique?No13-11Foreign Key ConsiderationsDesired ActionAppropriate Solution Drop parent tableCa
6、scade constraints Truncate parent tableDisable or drop foreign keyDrop tablespace containing parent tableUse the CASCADE CONSTRAINTS clausePerform DML on child tableEnsure the tablespace containing the parent key key is online13-1213-13Defining Constraints While Creating a Table CREATE TABLE hr.empl
7、oyee( id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEXSTORAGE(INITIAL 100K NEXT 100K)TABLESPACE indx, last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER(7) TABLESPACE users;13-1413-1513-1613-1713-18Guidelines for Defining ConstraintsPrimary and
8、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 initial load Defer constraint checking13-19Enabling ConstraintsENABLE NOVALIDATEALTER TABLE hr.departments ENABLE NOVALIDATE
9、CONSTRAINT dept_pk;No locks on table Primary and unique keys must use nonunique indexes13-2013-2113-22Enabling ConstraintsENABLE VALIDATEALTER TABLE hr.employees ENABLE VALIDATE CONSTRAINT emp_dept_fk;Locks table Can use unique or nonunique indexes Needs valid table data13-2313-24Using the EXCEPTION
10、S TableCreate 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.13-2513-2613-27Obtaining Constraint Informatio
11、nData Dictionary Views DBA_CONSTRAINTS DBA_CONS_COLUMNS13-2813-2913-30SummaryIn this lesson, you should have learned how to: Implement data integrity Use an appropriate strategy for creating and maintaining constraints Obtain information from the data dictionary13-3113-32Practice 13 OverviewThis pra
12、ctice covers the following topics: Creating constraints Enabling unique constraints Creating an Exceptions table Identifying existing constraint violations in a table, correcting the errors and re-enabling the constraints13-3313-341313-36ObjectivesAfter completing this lesson, you should be able to
13、do the following: Manage passwords using profiles Administer profiles Control use of resources using profiles Obtain information about profiles, password management, and resources13-37ProfilesA profile is a named set of password and resource limits Profiles are assigned to users by the CREATE USER o
14、r ALTER USER command Can be enabled or disabled Can relate to the DEFAULT profile13-3813-39Password ManagementUserPassword expiration and agingPassword verificationPassword historyAccount lockingSetting up profiles13-40Enabling Password ManagementSet up password management by using profiles and assi
15、gn them to users. Lock, unlock, and expire accounts using the CREATE USER or ALTER USER command. Password limits are always enforced.13-41Password Account LockingParameterFAILED_LOGIN_ATTEMPTSPASSWORD_LOCK_TIME DescriptionNumber of failed login attempts before lockout of the accountNumber of days th
16、e account is locked after the specified number of failed login attemptsPASSWORD_LOCK_TIME 13-42Password Expiration and AgingParameterLifetime of the password in days after which the password expiresGrace period in days for changing the password after the first successful login after the password has expiredPASSWORD_LIFE_TIMEPASSWORD_GRACE_TIMEParameter13-43Password HistoryParameterPASSWORD_REUSE_TIMEPASSWORD_REUSE_MAXDescriptionNumber of days before
