Data Integrity

1
Data Integrity

• Chapter Eight

2
Referential Integrity

• Main type of data validation within relational
  database
• Ensures relationships are maintained between the
  data in one table with data in another table
• Performed when data is being changed
• Performed during Insert, Update and Delete
• Statements will fail if Referential Integrity is
  not maintained

3
Concept of Referential Integrity

• Is a relationship between the data in two columns
  in different tables
• One column is the Primary Key of one table and
  contains a List of Valid Values
• The other column is the Foreign Key and contains
  data that is validated against the List
• The Table containing the List is the LOOKUP or
  REFERENCE or PARENT Table
• The Other is the DATA Table or CHILD Table

4
Concept of Referential Integrity

• The data in both columns is allowed to change
• The Foreign key can ONLY contain values that are
  also in the primary key column
• The Foreign key can contain NULL values
• A new Primary Key can be inserted in the Lookup
  Table, but an update or delete is restricted if
  it would remove a value that is used as a Foreign
  Key

5
Concept of Referential Integrity

• In the Data Table, a value can always be Deleted
  or set to null, but any new value must must pass
  validation
• Before Referential Integrity can be setup,a
  primary key must be created, or at least a unique
  index

6
Example of Referential Integrity

• STATE Table is the lookup table
• CLIENT Table is the data table
• Referential integrity setup between the
  State_Code columns of both tables
• Referential Integrity is setup via the ALTER
  Table statement, ADD Constraint, Foreign Key
  Constraint
• Note Run Sqlfun_drop_contraints.sql

7
Inserts and Updates Prevented by Referential
Integrity

• Pg 321 can insert in sec0802_clients Table a
  new primary key of 700
• Pg 321 cannot update client_id 200 to
  state_code MA
• MA is not a primary key in sec0802_states Table

8
How to Delete a code from a Lookup Table

• Must ensure that the primary key is not being
  used as a Foreign key by any row in the Data
  Table
• If being used, update the row in the Data Table
  using Nulls
• After updating with Nulls, the row in the Lookup
  Table may be deleted

9
RI as a Relationship between the Tables

• RI is a relationship between the data in two
  columns
• RI is also a relationship between two tables
• Lookup Table
• Data Table
• Two Parts
• Insert Rows into the Lookup Table before any rows
  can be inserted into the Data Table
• Cannot Drop either Table until we drop the
  Referential Integrity relationship

10
Delete Option of RI

• Three Options for handling Deletes
• Restrict (most common form)
• Set Null
• Cascade

11
SET NULL

• All the matching values in the foreign key column
  are automatically changed to null
• The rest of the data in the row is unchanged

12
Cascade Deletes

• The entire row is deleted from the data table
  when there is a matching value in the foreign key
  column

13
Cascade Updates

• All the matching values in the foreign key column
  are automatically changed to the new value
• The rest of the data in the row is unchanged

14
Two meanings of Primary Key

• When talking about Tables
• Primary Key is is the unique identifier of each
  row
• When talking about Referential Integrity
• Primary Key as the list of valid values in the
  Lookup Table
• New Option the list of valid values can be a
  column that is NOT the primary key but have a
  unique index defined on the column

15
Two or more Columns for a Primary Key

• Some Primary Keys are composed of multiple
  columns to provide the unique Primary Key
• L_foods Table foreign key
• Supplier_id, Product_code
• L_lunch_items Table primary key
• Supplier_id, Product_code

16
Other Types of Constraints

• CHECK Constraints
• Ensures that some statement about the data is
  true for every row in the table
• UNIQUE Constraints
• Ensures that every row contains a different value
• NOT NULL Constraints
• Ensures that there are no nulls in that column
• PRIMARY KEY Constraints
• Combination of Unique and Not Null constraint

17
Restrictions on the Datatypeand Length of Fields

• The Datatype definition for each column of a
  table functions as a constraint
• It limits the data that can be entered into the
  column
• It limits the datatype of the data
• It limits the length of the data

18
Constraints Coded in CREATE Table Statement

• Coding constraints in Create Table statements
  puts all of the code in one place for a Table
• During development, may be coded in separate
  statements for debug
• May be combined after testing and debugging