Foreign Key

Benefit

Referential Integrity: By adding a foreign key constraint, you enforce referential integrity between two related tables. This ensures that the values in the foreign key column of the referencing table (child table) correspond to valid values in the referenced table (parent table). It prevents the creation of orphaned or inconsistent data where references to non-existent rows would be allowed.
Data Understanding: Foreign keys provide a clear indication of the relationships between tables, making the database schema more understandable and navigable for developers and administrators. They serve as documentation that helps users comprehend the structure and dependencies within the database.

Drawback

Performance Impact: In certain scenarios, the presence of foreign keys can impact performance. Inserting, updating, or deleting records in tables with foreign key constraints may require additional checks and validations, which can slow down these operations. Moreover, cascading updates or deletes can lead to increased processing time.
Data Modification Constraints: Foreign keys can restrict certain data modification operations. For example, if a foreign key constraint exists, you may not be able to delete a record from the referenced table if there are dependent records in the referencing table. This constraint can sometimes limit the flexibility to perform certain actions.

Update / Delete Behaviors

ALTER TABLE referencing_table
ALTER CONSTRAINT fk_constraint_name
ON DELETE CASCADE;

CASCADE: When a row in the referenced table is deleted, all related rows in the referencing table are automatically deleted as well. This ensures that there are no orphaned records in the referencing table.
SET NULL: When a row in the referenced table is deleted, the foreign key values in the referencing table are set to NULL. This is useful when you want to retain the referencing records but remove the association.
SET DEFAULT: Similar to SET NULL, but instead of setting foreign key values to NULL, they are set to their default values defined in the table schema.
NO ACTION/RESTRICT: Prevents the deletion of a row in the referenced table if there are dependent rows in the referencing table. It acts as a constraint, enforcing referential integrity.

PreviousNormalization NextRelationship

Last updated 6 months ago

Was this helpful?

Benefit

Referential Integrity: By adding a foreign key constraint, you enforce referential integrity between two related tables. This ensures that the values in the foreign key column of the referencing table (child table) correspond to valid values in the referenced table (parent table). It prevents the creation of orphaned or inconsistent data where references to non-existent rows would be allowed.

Data Understanding: Foreign keys provide a clear indication of the relationships between tables, making the database schema more understandable and navigable for developers and administrators. They serve as documentation that helps users comprehend the structure and dependencies within the database.

Drawback

Performance Impact: In certain scenarios, the presence of foreign keys can impact performance. Inserting, updating, or deleting records in tables with foreign key constraints may require additional checks and validations, which can slow down these operations. Moreover, cascading updates or deletes can lead to increased processing time.

Data Modification Constraints: Foreign keys can restrict certain data modification operations. For example, if a foreign key constraint exists, you may not be able to delete a record from the referenced table if there are dependent records in the referencing table. This constraint can sometimes limit the flexibility to perform certain actions.

Update / Delete Behaviors

ALTER TABLE referencing_table
ALTER CONSTRAINT fk_constraint_name
ON DELETE CASCADE;

CASCADE: When a row in the referenced table is deleted, all related rows in the referencing table are automatically deleted as well. This ensures that there are no orphaned records in the referencing table.

SET NULL: When a row in the referenced table is deleted, the foreign key values in the referencing table are set to NULL. This is useful when you want to retain the referencing records but remove the association.

SET DEFAULT: Similar to SET NULL, but instead of setting foreign key values to NULL, they are set to their default values defined in the table schema.

NO ACTION/RESTRICT: Prevents the deletion of a row in the referenced table if there are dependent rows in the referencing table. It acts as a constraint, enforcing referential integrity.