Chapter 6, Part 2

• Kroenke goes through a number of id dependent relationships
• In every case, the solution is as expected.
• 
• 
• Minimal Cardinality
  • We can classify a relationship with the values
    • Optional
    • Mandatory
  • There are four such relationships for minimal cardinality.
    • O-O
    • O-M
    • M-O
    • M-M
  • Enforcing these becomes somewhat problematical.
  • Most DBMS do not provide a mechanism for forcing a parent to have a child (O-M and M-M)
  • What is worse, M-M is essentially a race condition.
  • We need to consider three situations
    • Insert
    • Modify
    • Delete
  • These are summarized in the following table
  • 
  • And we use three tools
    • Referential integrity constrains
    • Application
    • Triggers
  • Referential integrity constrains
    • Provide the first level tool for this action
    • If a foreign key is required, the database will prohibit insertion/deletion/modification if such a key is not available.
    • But it will do more than that.
    • Manual pages
    • Plus an sql script in ~dbennett/313/ch6
    • We can set conditions for what happens on update and delete of a foreign key.

    • CONSTRAINT constraint_name FOREIGN KEY (column)
          REFERENCES table name (column name)
          ON DELETE reference option
          ON UPDATE reference option
      	     

    • Reference Options
      • Restrict
        • Don't allow the action if it would cause a problem.
        • This is the default.
      • SET NULL
        • Set the foreign key field to NULL in the child when a parent is changed
        • Problems if the field can not be null
        • Remember, Primary key fields can not be null
        • Nor can more than one field where it is marked unique.
        • My example is contrived here.
      • NO ACTION
        • This is a SQL standard
        • And is equivalent to REJECT in MySQL
      • CASCADE
        • Whatever change occurred to the parent, propagate to the child.
        • Our author notes that this is mostly for weak entries, especially for delete
        • (Employee - phone number)
        • Strong entries can stand alone if one is changed
        • (Department - Employee)
• The example:
  • 
  • ~dbennett/313/ch6/ex1.sql