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'.

Outlines

00:00

💻 Database Table Creation with Foreign Keys

This paragraph describes the process of creating a database table named 'table barang' with various columns including 'barang ID', 'nama barang', 'harga beli', 'harga jual', 'supplier ID', and 'satuan ID'. The 'barang ID' is set as an auto-incrementing primary key. The 'supplier ID' and 'satuan ID' are defined as foreign keys that reference the 'supplier' and 'satuan' tables, respectively. The speaker emphasizes the need to create indexes before defining foreign keys. The foreign key constraints are set with 'on delete restrict' and 'on update cascade' to ensure referential integrity and automatic updates. The paragraph also discusses the importance of defining primary and foreign keys correctly to maintain database relationships.

05:01

🛠️ Troubleshooting and Creating Foreign Keys in Database Management Tools

The second paragraph focuses on troubleshooting common errors when creating a table with foreign keys and the steps to create such tables using database management tools like phpMyAdmin and HeidiSQL. It mentions the need to correct syntax errors, such as missing parentheses, and to set proper index names to avoid conflicts. The paragraph explains how to add foreign key constraints in phpMyAdmin by accessing the 'relation' view and filling in the properties for the foreign key. It also covers the process in HeidiSQL, where after creating a new table, you can add columns and define indexes before setting up the foreign key constraints. The speaker prefers using 'cascade' for updates to simplify the process of maintaining referential integrity across related tables.

Keywords

💡Table

In the context of the video, a 'table' refers to a structured collection of data in a database, organized into rows and columns. The video script describes the creation of a new table named 'table barang', which is used to store information about items or products. This table is central to the video's theme of database management and schema design.

💡Column

A 'column' in the video script represents an individual field or attribute within a table that holds data for a specific category. The script outlines several columns such as 'barang ID', 'nama barang', 'harga beli', 'harga jual', 'supplier ID', and 'satuan ID', each designed to store different types of information about the items in the 'table barang'.

💡Integer

The term 'integer' is used in the script to define the data type for certain columns in the table, such as 'barang ID', 'supplier ID', and 'satuan ID'. In database terminology, an integer is a whole number, which is suitable for storing values like IDs where decimal points are not applicable. The video emphasizes the use of integers for primary keys and identifiers.

💡VARCHAR

In the script, 'VARCHAR' is mentioned as the data type for the 'nama barang' column, which is used to store variable-length strings, such as the names of items. VARCHAR is a common data type in SQL for storing character strings, allowing for flexibility in the length of the data, which is essential for names that can vary in length.

💡NOT NULL

The 'NOT NULL' constraint mentioned in the script is used to ensure that certain columns do not allow NULL values. This is critical for columns like 'harga beli' and 'harga jual', where every item must have a defined buying and selling price. The video uses 'NOT NULL' to enforce data integrity and completeness in the database.

💡AUTO_INCREMENT

The 'AUTO_INCREMENT' attribute is used for the 'barang ID' column in the script to automatically generate a unique identifier for each new record. This feature is essential in database design for primary keys, ensuring that each item has a unique ID without manual intervention, as illustrated in the video's discussion on table creation.

💡Foreign Key

A 'Foreign Key' in the video script refers to a column or set of columns that refer to the primary key of another table, establishing a link between the two tables. The script describes setting up foreign keys for 'supplier ID' and 'satuan ID', which reference the 'supplier' and 'satuan' tables, respectively. This is a key concept in relational database design for maintaining referential integrity.

💡ON DELETE

The 'ON DELETE' clause in the script is part of the foreign key constraint and specifies the action to be taken when a referenced record in the parent table is deleted. The video discusses using 'ON DELETE RESTRICT' to prevent deletion of a parent record if it is still referenced by child records, ensuring data consistency.

💡ON UPDATE

Similar to 'ON DELETE', 'ON UPDATE' in the script dictates the behavior when a referenced record in the parent table is updated. The video mentions a preference for 'ON UPDATE CASCADE', which automatically updates the child records to match the changes in the parent record, streamlining data maintenance across related tables.

💡Primary Key

A 'Primary Key' is a column or a set of columns that uniquely identifies each row in a table. In the video, 'barang ID' is defined as the primary key for the 'table barang', ensuring that each item can be distinctly identified. The primary key is a fundamental concept in database design, as it serves as the basis for indexing and relationships with other tables.

💡Index

An 'Index' in the script refers to a database object that improves the speed of data retrieval operations on a table. The video mentions creating indexes for 'supplier ID' and 'satuan ID' before defining foreign keys, which is a common practice to enhance performance when accessing and joining tables based on these columns.

Highlights

Creating a new table named 'table barang' with specific column definitions.

Defining 'barang ID' as an integer with auto-increment, not null.

Setting 'nama barang' with a varchar length of 50, not null.

Establishing 'harga beli' as an integer, not null.

Setting 'harga jual' as an integer, not null.

Adding 'supplier ID' and 'satuan ID' as integers to reference other tables.

Describing the relationship of 'supplier ID' and 'satuan ID' as foreign keys referencing 'supplier' and 'satuan' tables.

Mentioning the need to create indexes before defining foreign keys.

Defining indexes for 'supplier ID' and 'satuan ID'.

Setting up foreign key constraints for 'supplier ID' with 'on delete restrict'.

Discussing the implications of 'on delete restrict' and 'on delete cascade'.

Explaining the use of 'on update cascade' for 'supplier ID'.

Creating a foreign key for 'satuan ID' with similar constraints as 'supplier ID'.

Defining 'barang ID' as the primary key of the table.

Mentioning the importance of closing the table creation query with a semicolon.

Discussing the process of creating a table with foreign keys in phpMyAdmin.

Explaining the process of adding foreign keys in HeidiSQL by creating a new table and defining columns.

Describing the steps to add foreign keys in HeidiSQL, including naming the constraint and selecting the reference table and column.

Transcripts

Oke selanjutnya kita akan Coba buat satu

table lagi bernama table barang

oke kolom-kolomnya adalah yang pertama

barang ID saya kasih Inter integer not

Nul auto

increment lalu nama

barangnya saya kasih varchar panjangnya

50 not n

juga lalu selanjutnya saya kasih harga

belinya

integer not

Nul selanjutnya saya kasih harga

jualnya inteer not

N0 selanjutnya saya kasih supplier

ID

integer dan juga satuan id

integer

Oke Nah di sini dapat dilihat ya

bahwasanya di sini saya mendefinisikan

supplier ID dan satuan ID ya supplier ID

nanti akan mereferensi ke tabel supplier

ya satuan ID juga akan mereferensi ke

tabel satuan ya Jadi nanti bareng

menjadi Child untuk parent-nya ya

parent-nya yaitu satuan dan supplier

Oke Oleh karena itu ini yang dinamakan

dengan Foreign Key ya sebelum membuat

forign ke kita harus membuat indeksnya

terlebih dahulu ya jadi di sini saya

definisikan

indek lalu di sini saya berikan indeks

untuk supplier

ID lalu indeks

untuk satuan

ID baru di sini saya eh Deskripsikan

foreign ke-nya forign ke-nya yaitu untuk

supplier id

dulu ya ID dulu yaitu references ya mau

mereference ke tabel yang mana yaitu ke

tabel supplier di kolom yang mana ya di

kolom yang mana tadi kalau misalkan kita

lihat itu supplier ID ya di kolom

supplier

id lebih baik sih ininya sama aja ya Ini

sama aja cuman e karena sudah terlanjur

tidak apa-apa Lalu nanti ada perintah

yang namanya

deleteah on delete ini adalah perintah

jika nanti saya melete parentnya ya jika

saya nanti misalkan di sini Saya sudah

punya barang dengan supplier toko toko a

misalkan lalu di table parentnya ya toko

A itu saya mau hapus itu apa yang akan

dilakukan di tabel barang ini ya jika on

deletnya restri ya restri nanti tablenya

tidak akan bisa dihus lama masih ada

child-nya di tabel barang ya jika on

delate-nya restrict jika on delete-nya

cascate ya jika nanti saya hapus di

table parent supplier maka table Child

yang ada di tabel barang nanti akan ikut

terhapus ya saya lebih suka menggunakan

restrict agar aman gitu ya jadi nanti

tidak ee Tidak sembarangan orang mau

menghapus dari tabel supplier ya Jika

masih ada barangnya yang mereferensi ke

tabel supplier tersebut maka tidak akan

bisa di-delete ya Ada juga satu lagi on

update ya on update sifatnya sama ya Ada

cascade ada restrict ya kalau update

Saya lebih suka pakai cascate ya Jadi

tidak merepotkan nanti ketika mau update

tinggal ee misalkan di sini saya mau

update ID ID supplier-nya ya nanti yang

di table barang akan ikut terupdate gitu

ya nanti kalau misalkan restrict jika

saya mau update di sini maka di sini

tidak bisa ter maka eh update-nya akan

gagal karena yang di sini masih ada

child-nya ya jadi itu adalah restrict

dan update eh restrict dan cascate yang

bisa diterapkan di on delete dan on

update ya lalu satu lagi saya buat

foreign key-nya untuk satuan ID ya

mereferensikan

ke

satuan Ya akan saya samakan on

delete-nya restrict juga on update-nya

juga

skate oke lalu saya definisikan primary

key-nya yaitu adalah barang ID

oke enter saya tutup lalu saya kasih

titik koma Oh ini kurang tutup ya ini

kurang tutup Oke kita bisa ulangi nah

ini adalah repotnya jika kita

menggunakan comen from Ya kita harus

satu-satu seperti ini

ya tidak apa-apa sekaligus kita tahu

gimana caranya kalau misalkan ada error

ya oke di sini kita harus tambahkan

tanda kurungnya tinggalan tadi

oke indeksnya sudah on delete restrict

on update case gate sepertinya

Oke enter

oke query oke no R affected ya Ini

adalah cara untuk membuat table yang

mempunyai kunci forign ke Oke hal ini

juga kita bisa lakukan di php my admin

dan juga di hidsql

Oke di php my admin caranya adalah

dengan pertama kita buat tableennya dulu

ya kita buat tablenya dulu lalu setelah

membuat table ya jadi tidak bisa

langsung ketika buat table langsung buat

foring Sepertinya kalau di php my admin

kita harus buat table-nya dulu lalu kita

klik relation view relation view ada di

Tab struktur ya di forign key constraint

ya di sini Biarkan saja lalu ke forign

key constraint langsung ya forign key

constraint langsung di sini ada

properties-prerties untuk ngisi forign

ke-nya ya di sini karena sudah saya buat

sebelumnya ya Nah ini adalah nama

indeksnya nama indeksnya terserah nama

indeksnya terserah namun jangan sampai

sama dengan nama indeks yang lain ya

harus dibedakan kalau saya membuat

melalui comand Prom tadi ya ini saya

tidak perlu menuliskan nama indeksnya

sudah akan dibuat secara otomatis oleh

eh mycale ya lalu selanjutnya adalah

kolom dari tabel barang kolom dari tabel

barang yang ingin dibuat foren ke-nya

lalu di sini adalah references-nya ya

references-nya lalu di sini ya on delete

sama on update-nya ya Tadi ada restrict

dan ada casgate di sini juga ada pilihan

lain yaitu set null dan no action ya

cuman e yang lebih sering dipakai adalah

casgate dan restrict saja ya di sini

kita bisa tambahkan beberapa forignk

sekaligus di sini lalu kita bisa Klik

simpan ya Ini adalah cara untuk membuat

fornk di php my admin Lalu bagaimana

caranya untuk menambahkan table dengan

forign di hdsl ya kita bisa buat tabel

baru

oke tabel baru lalu di sini saya

ketikkan nama tabelnya misalkan saya

untuk contoh saja barang dua lalu

misalkan saya tambahkan tiga kolom di

sini yang pertama barang ID lalu

langsung saja supplier id

lalu langsung saja juga satuan ID oke

lalu jangan dilupakan

indeksnya barang Id adalah primary nah

supplier ID ya kita juga buat index key

ya ini adalah Eh sama dengan seperti

saya membuat indeks seperti ini ya key

lalu juga di sini juga saya tambahkan

key

baru oke Setelah itu saya bisa buat

foreign key-nya

foreign key-nya yang pertama bisa saya

klik tambah ya dinamakan dinamakan

misalkan di sini supplier FK ya foren

kolomnya supplier ID tabel

referensinya supplier kolom

foren-nya supplier ID oke lalu saya bisa

tambah lagi misalkan satuan id-nya tadi

satuan forign ke ya namaak ini ya

seperti saya utarakan tadi ini terserah

ya Yang penting jangan sama satuan ID

tabel referensinya satuan kolom forign

key-nya satuan ID Oke on update on

delete-nya Saya cascate di sini

cascate ya setelah itu sebenarnya bisa

kita klik kode create-nya dulu ya hampir

sama ya barang dua barang ID supplier ID

Primary Key nah ini ya

indeksnya seperti saya putarkan tadi Nah

di sini ada constrain-nya ini adalah

nama forign ke-nya

Oke sudah kita bisa Klik

simpan ya Ini adalah cara untuk membuat

table yang yang memiliki forignk pada

comand Prom lalu pada PHP my admin dan

pada heid SQL