Foreign Key #6 | MySQL | Bahasa Indonesia

Summary

The video script provides a detailed tutorial on creating a 'table barang' with specific columns and attributes in a database. It explains the process of defining columns such as 'barang ID', 'nama barang', 'harga beli', 'harga jual', 'supplier ID', and 'satuan ID', and setting them with appropriate data types and constraints. The script further elaborates on establishing foreign key relationships with 'supplier' and 'satuan' tables, including the use of 'on delete' and 'on update' clauses with 'restrict' and 'cascade' options. It also covers the creation of foreign keys in phpMyAdmin and HeidiSQL, emphasizing the importance of indexing and primary keys for relational integrity.

Takeaways

• 📝 The script discusses creating a new table named 'table barang' with specific columns and data types.
• 🔑 It emphasizes defining primary keys ('barang ID') and foreign keys ('supplier ID' and 'satuan ID') with their respective references to other tables.
• 🛠️ The script mentions creating indexes for foreign keys before defining them to ensure proper database relationships.
• 🗂️ The 'on delete' and 'on update' clauses are explained, with options like 'restrict' and 'cascade' to manage referential integrity.
• 🚫 The use of 'restrict' is recommended over 'cascade' for 'on delete' to prevent accidental deletions that could leave orphaned records.
• 🔄 For 'on update', 'cascade' is preferred to automatically update related records in child tables when changes are made in the parent table.
• 💾 The script outlines the process of creating tables with foreign keys in both command-line interfaces like MySQL and graphical tools like phpMyAdmin and HeidiSQL.
• 🖥️ In phpMyAdmin, tables must be created first before defining foreign keys, which is done through the 'Foreign key constraints' section under 'Structure'.
• 💻 In HeidiSQL, creating a new table allows for immediate addition of foreign keys, with a straightforward interface to define relationships and constraints.
• 📋 The script highlights the importance of naming indexes uniquely to avoid conflicts and ensure clear database structure.
• 🔍 It provides a detailed walkthrough of the steps involved in defining table structures, including primary and foreign keys, and the implications of different 'on delete' and 'on update' options.

Q & A

What is the purpose of creating a 'table barang' as described in the transcript?

The purpose is to create a new table named 'table barang' to store information about items, including their ID, name, purchase price, selling price, supplier ID, and unit ID, with appropriate constraints and relationships to other tables.

What data types and constraints are assigned to the 'barang ID' and 'nama barang' columns in the 'table barang'?

The 'barang ID' is assigned an 'INT' data type with 'NOT NULL', 'AUTO_INCREMENT' properties, and the 'nama barang' is given a 'VARCHAR' data type with a length of 50 and also marked as 'NOT NULL'.

How are the 'harga beli' and 'harga jual' columns defined in the 'table barang'?

Both 'harga beli' and 'harga jual' columns are defined as 'INTEGER' data types with 'NOT NULL' constraints, indicating that these fields must always have a value and cannot be left empty.

What is the significance of 'supplier ID' and 'satuan ID' in the 'table barang'?

The 'supplier ID' and 'satuan ID' are foreign keys in the 'table barang' that establish relationships with the 'supplier' and 'satuan' tables, respectively. They are used to reference the supplier and unit of measurement for each item.

Why is it necessary to create indexes before defining foreign keys in the 'table barang'?

Indexes are necessary before defining foreign keys to ensure that the database can efficiently look up and match the values in the foreign key columns with the referenced primary keys in the related tables.

What is the difference between 'ON DELETE RESTRICT' and 'ON DELETE CASCADE' as foreign key actions in the 'table barang'?

With 'ON DELETE RESTRICT', the deletion of a referenced row in the parent table (supplier or satuan) is not allowed if there are dependent rows in the child table (table barang). 'ON DELETE CASCADE', on the other hand, automatically deletes the dependent rows in the child table when a referenced row in the parent table is deleted.

What does 'ON UPDATE CASCADE' mean in the context of the 'table barang'?

'ON UPDATE CASCADE' means that if the value of the referenced key in the parent table (supplier or satuan) is updated, the corresponding foreign key values in the child table (table barang) will also be updated automatically.

Why might someone choose 'ON DELETE RESTRICT' over 'ON DELETE CASCADE' when defining foreign keys?

Choosing 'ON DELETE RESTRICT' over 'ON DELETE CASCADE' is a way to prevent accidental deletions of parent records that are still referenced by child records, thus ensuring data integrity and avoiding orphaned records in the child table.

How can the process of creating a table with foreign keys be done in phpMyAdmin?

In phpMyAdmin, one must first create the table without foreign keys. After the table is created, navigate to the 'Structure' tab, then to 'Foreign key constraints' to define the foreign keys and their constraints.

What is the process of adding a table with foreign keys in HeidiSQL?

In HeidiSQL, you can create a new table and add columns directly. After defining the primary key and any necessary indexes, you can add foreign keys by specifying the foreign key name, the column, the referenced table and column, and the actions for 'ON DELETE' and 'ON UPDATE'.