Tutorial Database : MySQL Foreign Key | Bahasa Indonesia ~ Part 6

Summary

In this tutorial, Fajar explains how to use foreign keys in MySQL to manage data relationships between tables, specifically for student and department tables. He demonstrates how foreign keys help simplify data management by storing IDs rather than full data, making updates and deletions easier. Fajar walks through the process of creating foreign keys, handling referential integrity with actions like 'ON DELETE RESTRICT' and 'ON DELETE CASCADE,' and showcasing how changes in one table automatically reflect in related tables. The video also briefly touches on JOIN queries to retrieve linked data.

Takeaways

• 😀 Foreign Keys in MySQL help maintain data integrity by referencing data from another table, instead of duplicating it.
• 😀 Using Foreign Keys, you can change data in the reference table (e.g., department name) without affecting the related records in other tables.
• 😀 Updating a department name in the 'jurusan' table will automatically reflect in all related student records, saving time and effort.
• 😀 To implement a Foreign Key, you must first remove the reference column (e.g., department name) from the student table and replace it with a department ID.
• 😀 The SQL syntax for adding a Foreign Key involves using 'ALTER TABLE' to add a new column and define the relationship with another table.
• 😀 By using JOIN queries, you can display related data from multiple tables, such as showing student names alongside their department names.
• 😀 Foreign Keys allow you to manage updates and deletions efficiently, without having to manually update or delete related data across multiple records.
• 😀 With the 'ON DELETE CASCADE' option, deleting a record in the referenced table will automatically delete all related records in the referencing table.
• 😀 The 'ON DELETE RESTRICT' option prevents the deletion of a record in the referenced table if there are related records in the referencing table.
• 😀 Foreign Keys help prevent data inconsistency by ensuring that updates and deletions are consistent across related tables, reducing manual intervention.

Q & A

What is a foreign key in MySQL?

A foreign key in MySQL is a reference from one table to another, typically used to link data between two tables. It stores the ID of a related record from another table rather than storing the entire data, which helps maintain data integrity and allows easier updates or deletions.

Why would you use a foreign key to reference a column like 'jurusan' (department)?

Using a foreign key to reference a column like 'jurusan' ensures that instead of storing the department's name in each row of the 'murid' (student) table, we store just the department's ID. This approach makes it easier to update the department name in one place (the 'jurusan' table), and all related data in the 'murid' table will automatically reflect this change.

What is the problem with directly storing the department's name in the 'murid' table?

If we store the department's name directly in the 'murid' table, any changes to the department name (e.g., from 'IPA' to 'Ilmu Alam') would require updating every occurrence of that department name in the table. This can become very cumbersome, especially with large datasets, and can lead to data inconsistency.

How do you implement a foreign key relationship in MySQL?

To implement a foreign key relationship, you first add a column in the referencing table (like 'jurusan_ID' in 'murid') to store the reference ID. Then, you use the 'ALTER TABLE' command to add the foreign key constraint, linking this column to the primary key of the referenced table (like 'ID' in 'jurusan').

What is the significance of the 'ON DELETE' clause when working with foreign keys?

The 'ON DELETE' clause defines how changes in the referenced table (e.g., deletion of a department) will affect the referencing table (e.g., 'murid'). You can specify 'CASCADE' to delete related records, 'RESTRICT' to prevent deletion if related records exist, or 'SET NULL' to set the foreign key value to NULL if the referenced record is deleted.

What happens if you delete a row in the 'jurusan' table with a foreign key set to 'ON DELETE CASCADE'?

If the foreign key is set to 'ON DELETE CASCADE', deleting a department (row) in the 'jurusan' table will automatically delete all related rows in the 'murid' table, where the foreign key reference matches the deleted department's ID.

How does 'ON DELETE RESTRICT' differ from 'ON DELETE CASCADE'?

'ON DELETE RESTRICT' prevents the deletion of a row in the 'jurusan' table if there are related rows in the 'murid' table. It enforces that the referenced data cannot be deleted as long as it is being referenced, while 'ON DELETE CASCADE' will delete all dependent data in the 'murid' table when the referenced data is deleted.

What is the purpose of using the 'JOIN' operation in SQL when dealing with foreign keys?

The 'JOIN' operation is used to combine data from two or more tables based on related columns, such as foreign key references. For example, you can use 'JOIN' to display the department name from the 'jurusan' table alongside the student data in the 'murid' table, even though the 'murid' table only stores the department ID.

How does the 'SELECT' query work in conjunction with foreign keys to retrieve data?

A 'SELECT' query, when combined with a 'JOIN' operation, can retrieve data from multiple tables by linking the foreign key in one table to the primary key in another. For example, you can use a 'SELECT' statement with a 'JOIN' to show student names along with their department names, even though the 'murid' table stores only the department ID.

What would happen if you try to delete a department that is referenced in the 'murid' table when 'ON DELETE RESTRICT' is used?

When 'ON DELETE RESTRICT' is used, attempting to delete a department that is referenced in the 'murid' table will result in an error. The deletion will be restricted because there are dependent records in the 'murid' table, and MySQL prevents the deletion to maintain data integrity.