Chapter 13

1. Chapter 13 Data Integrity

2. Types of Integrity • Database structure integrity • Semantic data integrity

3. Structure integrity • Types of structural problems • Index corruption (can happen when have to rebuild table and index not re-created properly) • Pointers – to other objects (very large objects) • Page header corruption • Backup files

4. Managing Problems • Utility programs check on different aspects of database structural integrity • Consistency options • DBCC CHECKTABLE • DBCC REINDEX

5. Managing continued • Database checking • DBCC CHECKDB • DBCC CHECKCATALOG • DBCC CHECKALLOC • Memory Usage • Configured memory • Code size • Kernel and structures • Page cache

6. continued • Procedure buffers and headers • Buffer cache detail • Procedure cache detail

7. Semantic Data Integrity • Ensure accuracy and viability of data contents of the database • Entity integrity – each entity must be uniquely identifiable (primary key) • Unique constraints – unique columns, but cannot be used for referential integrity • Data types – type and length; UDT

8. continued • Default values • Check constraints – values that can be stored in column • Triggers – even driven procedures • Access and modify other tables • Print informational messages • Specify complex restriction • Triggers can be fired either before or after firing activity

9. continued • Triggers fired by an INSERT, UPDATE or DELETE • Nested Triggers – do have limit on nesting • Can use triggers to implement referential integrity • Transition tables – two tables with before and after images

10. continued • Trigger granularity – can impact multiple rows of data (two levels) • statement-level trigger – executed once upon firing regardless of actual number of rows inserted, deleted or updated • Row-level trigger – once fired it is executed once for each and every row that is inserted, deleted or updated

11. Referential Integrity • Relationships between tables – primary and foreign key relationships • Need to determine the status of foreign key columns when primary key is deleted or updated • Parent table-child table

12. rules • Insert rule (try to insert a value into a foreign key without a corresponding primary key) – two rules • Never permissible • Whether actual values must be specified instead of nulls

13. Rules continued • Update rule – foreign key cannot be updated to a value that does not correspond to a primary key • Primary key perspective • Restricted UPDATE • Neutralizing UPDATE (set to null) • Cascading UPDATE • Foreign key perspective – allow updates

14. Rules continued • DELETE rule – try to delete row from parent table • Restricted DELETE (not allowed if foreign key exists) • Neutralizing DELETE • Cascading DELETE • Pendant DELETE – no foreign keys refer back to parent table – done tiwth triggers

15. Referential Integrity Rules of Thumb • Certain situations in which RI can be bypassed • Can have SQL switches that turn RI on and off • Primary and foreign keys can have different names, null qualifiers (NULL, NOT NULL) and default values

16. Rules continued • Multiple relationships exist for dependent row • If have composite primary key – a single row must exist in the parent table with key values that match all the columns of the foreign key for the row being inserted into the dependent table

17. Rule continued • Avoid using declarative RI tocreate very large groups of referentially related tables • Consider using CHECK constraints instead of referential integrity