Lec-11: Insert, Update & Delete from Foreign Key table | Referential Integrity

What is a Foreign Key?

Introduction to Foreign Keys

• The video introduces the concept of foreign keys, building on previous discussions in "Foreign Key Part One."

• It emphasizes the importance of understanding the fundamental concept of foreign keys and their role in database integrity.

Referential Integrity

• A key takeaway is that foreign keys maintain referential integrity, which is crucial for database management.

• The speaker highlights that many people know about foreign keys but lack an understanding of their underlying concepts.

Understanding Integrity

• Integrity in databases refers to consistent values across different data sources; discrepancies indicate a lack of integrity.

• An example involving mobile prices illustrates how varying prices reflect a lack of integrity compared to interconnected databases like those in universities.

Example Scenario: University Database

• The speaker uses a university scenario where student information must be consistent across various departments (e.g., placement records).

• If discrepancies arise (e.g., Ram being listed as an electronics student instead of CSC), it indicates a loss of integrity within the database.

How Foreign Keys Work

Structure of Tables

• The discussion shifts to table structures, identifying one as the referenced table (with primary key) and another as referencing table (with foreign key).

• The student table contains essential details such as roll numbers and names, while the course table links students to their respective courses using roll numbers.

Operations on Tables

• Inserting new entries into the referenced table (base table with primary key) does not create issues; multiple entries can be added without violation.

• However, when discussing deletion from this base table, potential problems may arise if linked data exists in referencing tables.

Data Integrity and Deletion in Databases

Understanding Data Deletion Risks

• The speaker discusses the implications of deleting data, emphasizing that removing a student's record (e.g., roll number 1) can lead to integrity issues if other tables still reference that student as being enrolled in a course.

• A specific example illustrates how deleting roll number 1 could create an inconsistency where the database indicates the student is studying DBMS despite their departure from the university.

• The speaker notes that deletion should be approached cautiously; for instance, deleting roll number 4 poses no risk since they haven't been assigned a course yet.

Solutions for Safe Deletion

• Two solutions are proposed for managing deletions: On Delete Cascade and On Delete Set Null. The former automatically removes references across related tables when a record is deleted.

• On Delete Set Null means that if roll number 1 is deleted, any references to it in other tables will be set to null instead of being removed entirely.

• Caution is advised when using these methods, especially if the foreign key also serves as a primary key, as primary keys cannot be null.

Default Behavior and Manual Management

• The default behavior when attempting to delete referenced data is On Delete No Action, which prevents deletion if there are existing references, thus maintaining data integrity.

• To successfully delete such records, one must first remove them from referencing tables before proceeding with deletion from the base table.

Updating Records Safely

• When updating records (e.g., changing roll number 1 to 10), care must be taken to avoid inconsistencies between base and referencing tables.

• An example highlights potential issues where changing a roll number without corresponding updates elsewhere leads to orphaned records or inconsistencies within the database structure.

Managing Insertions in Referencing Tables

• Inserting new data into referencing tables requires validation against existing records in base tables. For instance, adding a course for roll number 7 necessitates confirmation that this student exists in the base table.

Understanding Referential Integrity in Database Operations

Insertion and Potential Violations

• The speaker discusses the potential for insertion operations to cause violations in a database, emphasizing that it is not guaranteed to happen every time.

• If values being inserted already exist in the table, there may be no issue; however, caution is advised as it can lead to data integrity problems.

Deletion Operations

• Deleting records from a table (e.g., roll number 1) does not typically cause any issues or violations within the database.

• The speaker asserts that deletion will not create any problems, indicating that removing data does not affect referential integrity negatively.

Updation and Its Risks

• Updating records (like course ID or name) is generally permissible without causing issues unless critical identifiers like roll numbers are changed incorrectly.

• A specific example illustrates how changing a roll number from 2 to 20 could lead to inconsistencies and errors due to mismatched references.

Error Handling in SQL Operations

• The default behavior of SQL platforms (like Oracle or SQL Server) shows errors primarily during insertion attempts when violations occur but allows deletions without warnings.

• This highlights an important aspect of error handling: while insertions may trigger alerts for potential conflicts, deletions do not raise similar concerns.

Referential Integrity Concepts

• The discussion emphasizes understanding how foreign keys maintain referential integrity across different tables during insertion, deletion, and updating processes.