MIS 431

1
MIS 431

• Chapter 7.
• Enforcing Data Integrity

2
Types of Integrity

• Domain integrity
• Entity integrity
• Referential integrity
• User-defined business rules integrity

3
Domain Integrity

• Data type
• Length
• Minimum
• Maximum
• Format
• Valid range of values
• Specific list of values

4
Entity Integrity

• Each record identifiable by a single unique value
• Not null
• Not duplicated

5
Referential Integrity

• Foreign key
• Either null (often undesirable)
• Or matches value (usually the primary key) in
  another table

6
User-Defined Integrity

• Often rule-based
• May require stored procedure, trigger, or
  user-defined function

7
Types of Constraints

• PRIMARY KEY
• UNIQUE
• CHECK
• DEFAULT
• FOREIGN KEY
• RESTRICT / CASCADE
• OBJECTS
• DEFAULT
• RULE

8
PRIMARY KEY Constraints

• Natural attribute or surrogate key
• Cannot be NULL
• PK is not an index, but uses an index
• Common surrogates
• IDENTITY column
• GUID UNIQUEIDENTIFIER data type and NEWID default

9
UNIQUE Constraints

• Enforce uniqueness on other than PK fields
• Can have more than one per table
• Accepts NULL values
• Only one if constraint on a single field
• Must yield unique combination if constraint on
  multiple fields

10
CHECK Constraints

• Defined by any expression that returns TRUE or
  FALSE
• Can reference only columns in same row, same
  table
• Uses
• Validate format
• Check for data range
• Check for illegal values
• Check for intra-record consistency

11
DEFAULT Definitions

• Apply only in INSERT operations
• Default can be
• Constant
• System scalar function
• User-defined scalar function
• Scalar expression
• DEFAULT or NULL?

12
FOREIGN KEY Constraints

• INSERT and UPDATE only values that match
• DELETE cant delete if there is a matching child
• Solving conflicts when updating or deleting
• RESTRICT (SQL Server default)
• CASCADE
• SET NULL (not in SQL Server)
• SET DEFAULT (not in SQL Server)

13
T-SQL Objects

• DEFAULT objects
• Used to apply the same complex default value to
  several fields
• RULE objects
• Used to apply the same complex check constraint
  to several fields