2.11f

Null Values and Referential Integrity

• A value is fully null if all columns are null.

• Referential Integrity: A relational rule requiring foreign key values to either match a primary key value or be fully null.

• Foreign keys must obey referential integrity at all times.

• A composite key value is partially null if some, but not all columns are null.

• A partially null composite key can never match a primary key, as primary keys cannot be null.

Violations of Referential Integrity

• A null in a simple foreign key does not violate referential integrity.

• Referential integrity can be violated in four ways:

  1. Updating a primary key.

  2. Updating a foreign key.

  3. Inserting a row containing a primary key.

  4. Inserting a row containing a foreign key.

• Deleting a row containing a primary key does not violate referential integrity.

Handling Violations

• MySQL provides several actions to manage referential integrity violations:

  1. RESTRICT: Rejects inserts, updates, or deletes that would violate integrity.

  2. SET NULL: Sets invalid foreign keys to null, cannot be used if the foreign key does not allow null values.

  3. SET DEFAULT: Sets invalid foreign keys to a predefined default value (limited support in some MySQL configurations).

  4. CASCADE: Propagates changes from the primary key to associated foreign keys:

     • If a primary key is deleted, rows with matching foreign keys are also deleted.

     • If a primary key is updated, matching foreign keys are updated to reflect the change.

MySQL Support and Limitations

• ON UPDATE and ON DELETE clauses specify the actions taken regarding foreign keys during update or delete operations.

• MySQL supports only the four specified actions in these clauses.

• RESTRICT is applied when ON UPDATE or ON DELETE is omitted.

• SET NULL is not applicable if the foreign key cannot be null.

• SET DEFAULT is limited in some MySQL setups.

Effects of Foreign Key Deletion

• Deleting a foreign key has no effect on the primary key.