MySQL: “Kunci Asing” Digunakan dalam Contoh

Di bawah ini kami akan menunjukkan beberapa contoh sederhana tentang cara mengatur kunci asing dan cara kerjanya di MySQL. Dalam contoh kami, kami akan mereferensikan tabel induk dan anak di bawah ini. Tabel yang berlabel “Pelanggan” dan “Kontak” masing-masing mewakili tabel induk dan anak.


Pada dasarnya ada dua cara untuk mendefinisikan kunci asing di MySQL:

  1. Menentukan Kunci Asing dengan CREATE TABLE
  2. Mendefinisikan Kunci Asing dengan ALTER TABLE

Selain contoh-contoh ini, kami juga menyertakan diskusi tentang apa batasan kunci asing dan bagaimana mereka diformat, dengan contoh-contoh juga.

Berjaga-jaga – Apa Kunci Asing?

Kunci asing adalah bidang (atau kumpulan bidang) dalam tabel yang secara unik mengidentifikasi deretan tabel lain. Tabel di mana kunci asing didefinisikan disebut “tabel anak” dan itu (sering) mengacu pada kunci utama dalam tabel induk.Contoh-contoh Kunci Asing MySQL

Batasan kunci asing kemudian dapat digunakan untuk menentukan bagaimana integritas data ditegakkan antara dua tabel (mis., Ketika baris tabel dihapus atau diperbarui).

Jika dilakukan dengan benar, tautan antara kunci utama dan kunci asing akan selalu dipertahankan, sehingga basis data Anda tidak akan pernah ditinggalkan dengan catatan yatim di tabel anak (mis., Informasi kontak sisa yang terkait dengan pelanggan yang dihapus). Contoh basis data pelanggan yang disederhanakan (dan tidak sepenuhnya dinormalisasi) membantu kami memvisualisasikan konsep kunci asing ini.

1. Menentukan Kunci Asing dengan CREATE TABLE

Pergilah ke konsol MySQL dengan mengetikkan perintah mysql dengan argumen pengguna dan kata sandi yang benar. Jika perlu, ketikkan “man mysql” untuk mendapatkan informasi lebih lanjut.

1 mysql -u -p

Buat database dan mulai menggunakannya:

1
2

BUAT DATABASE testdb;

gunakan testdb;

Membuat Tabel

Sekarang buat dua tabel:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

BUAT pelanggan TABEL (

  id INT TIDAK NULL AUTO_INCREMENT,

  firstname varchar (50) NOT NULL,

  lastname varchar (50) NOT NULL,

  KUNCI UTAMA (id)

) ENGINE = INNODB;

 

BUAT kontak TABEL (

  id INT,

  customer_id INT,

  info varchar (50) NOT NULL,

  ketik varchar (50) NOT NULL,

  INDEX par_ind (customer_id),

  CONSTRAINT fk_customer FOREIGN KEY (customer_id)

  REFERENSI pelanggan (id)

  HAPUS HAPUS CASCADE

  PADA PEMBARUAN CASCADE

) ENGINE = INNODB;

Memverifikasi Struktur Tabel

Mari kita lihat seperti apa struktur basis data kami:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

TAMPILKAN TABEL;

+——————+

| Tables_in_testdb |

+——————+

| hubungi |

| pelanggan |

+——————+

 

DESCRIBE pelanggan;

+———– + ———— + +—— + —– + ——— + ——————-+

| Bidang | Ketik | Null | Kunci | Default | Ekstra |

+———– + ———— + +—— + —– + ——— + ——————-+

| id | int (11) | TIDAK | PRI | NULL | auto_increment |

| nama depan | varchar (50) | TIDAK | | NULL | |

| nama belakang | varchar (50) | TIDAK | | NULL | |

+———– + ———— + +—— + —– + ——— + ——————-+

 

DESCRIBE kontak;

+————- + ————- + —— + —– + ——— + ——-+

| Bidang | Ketik | Null | Kunci | Default | Ekstra |

+————- + ————- + —— + —– + ——— + ——-+

| id | int (11) | YA | | NULL | |

| customer_id | int (11) | YA | MUL | NULL | |

| info | varchar (50) | TIDAK | | NULL | |

| ketik | varchar (50) | TIDAK | | NULL | |

+————- + ————- + —— + —– + ——— + ——-+

Catat nilai MUL di kolom Key pada tabel kontak. Ini memberitahu kita bidang customer_id adalah kolom pertama dari indeks non-unik (artinya dapat memiliki beberapa baris dengan nilai yang sama).

Menambahkan Data ke Tabel

Sekarang kita dapat mengisi dua tabel basis data kita dengan beberapa data sampel. Pertama, tabel pelanggan:

1
2
3
4

Masukkan nilai pelanggan (nama depan, nama belakang)

(‘Elaine’, ‘Stevens’),

(‘Mary’, ‘Dittman’),

(‘Lewati’, ‘Stevenson’);

Tabel pelanggan kami sekarang terlihat seperti ini:

1
2
3
4
5
6
7
8

PILIH * DARI pelanggan;

+—- + ———– + ———–+

| id | nama depan | nama belakang |

+—- + ———– + ———–+

| 1 | Elaine | Stevens |

| 2 | Mary | Dittman |

| 3 | Lewati | Stevenson |

+—- + ———– + ———–+

Beberapa data sampel untuk tabel kontak:

1
2
3
4
5
6
7
8

Masukkan ke kontak (customer_id, info, ketik) VALUES

(‘1’, ‘111–111–111’, ‘kerja’),

(‘1’, ‘111–111–123’, ‘rumah’),

(‘1’, ‘[email protected]’, ‘email’),

(‘2’, ‘222–222–222’, ‘kerja’),

(‘2’, ‘[email protected]’, ‘email’),

(‘2’, ‘[email protected]’, ‘email’),

(‘3’, ‘[email protected]’, ‘email’);

Tabel kontak kami sekarang terlihat seperti ini:

1
2
3
4
5
6
7
8
9
10
11
12

PILIH * DARI kontak;

+—— + ————— + ——————– + ——-+

| id | customer_id | info | ketik |

+—— + ————— + ——————— +-+

| NULL | 1 | 111–111–111 | kerja |

| NULL | 1 | 111–111–123 | home |

| NULL | 1 | [email protected] | email |

| NULL | 2 | 222–222–222 | kerja |

| NULL | 2 | [email protected] | email |

| NULL | 2 | [email protected] | email |

| NULL | 3 | [email protected] | email |

+—— + ————— + ——————– + ——-+

Sekarang kami memiliki data sampel, mari kita lihat bagaimana kunci asing membantu menjaga integritas data.

Menghapus Data Referensi (untuk Melihat Bagaimana Integritas Data Dipelihara)

Sebelumnya kami mendefinisikan batasan kunci asing sebagai:

“REFERENSI KUNCI ASING (customer_id) pada pelanggan DELETE CASCADE ON UPDATE CASCADE”

Ini berarti bahwa ketika kami menghapus pelanggan tertentu, baris tabel kontak terkait juga harus dihapus. Selain itu, ON UPDATE CASCADE akan mengalirkan setiap pembaruan di tabel induk ke bidang yang direferensikan di tabel anak (dalam hal ini, customer_id).

Mari uji pertama menghapus pelanggan:

1 HAPUS DARI pelanggan DI MANA firstname = ‘Lewati’;

Ketika kita melihat tabel kita lagi, kita melihat bahwa kedua tabel diubah sesuai kebutuhan, karena semua baris kontak untuk Lewati juga dihapus:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

PILIH * DARI pelanggan;

+—- + ———– + ———-+

| id | nama depan | nama belakang |

+—- + ———– + ———-+

| 1 | Elaine | Stevens |

| 2 | Mary | Dittman |

+—- + ———– + ———-+

 

PILIH * DARI kontak;

+—— + ————— + ——————— +-+

| id | customer_id | info | ketik |

+—— + ————— + ——————– + ——-+

| NULL | 1 | 111–111–111 | kerja |

| NULL | 1 | 111–111–123 | home |

| NULL | 1 | [email protected] | email |

| NULL | 2 | 222–222–222 | kerja |

| NULL | 2 | [email protected] | email |

| NULL | 2 | [email protected] | email |

+—— + ————— + ——————— +-+

Memperbarui data referensial (untuk melihat bagaimana integritas data dipertahankan)

Sekarang mari perbarui customer_id dari Elaine, untuk menguji ON UPDATE CASCADE:

1 SET UPDATE pelanggan SET id = 7 WHERE firstname = ‘Elaine’;

Perubahan yang dibutuhkan sekarang telah dibuat untuk kedua tabel:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

pilih * dari pelanggan;

+—- + ———– + ———-+

| id | nama depan | nama belakang |

+—- + ———– + ———-+

| 7 | Elaine | Stevens |

| 22 | Mary | Dittman |

+—- + ———– + ———-+

 

pilih * dari kontak;

+—— + ————— + ——————— +-+

| id | customer_id | info | ketik |

+—— + ————— + ——————– + ——-+

| NULL | 7 | 111–111–111 | kerja |

| NULL | 7 | 111–111–123 | home |

| NULL | 7 | [email protected] | email |

| NULL | 22 | 222–222–222 | kerja |

| NULL | 22 | [email protected] | email |

| NULL | 22 | [email protected] | email |

+—— + ————— + ——————– + ——-+

2. Menentukan Kunci Asing dengan ALTER TABLE

Dimungkinkan juga untuk menambahkan kunci asing ke tabel setelah mereka telah dibuat. Alih-alih melakukan pembuatan tabel dalam satu langkah, seperti yang kami lakukan sebelumnya, kami juga dapat melakukannya dalam beberapa langkah.

Menciptakan & Kemudian Mengubah Tabel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

BUAT pelanggan TABEL (

  id INT TIDAK NULL AUTO_INCREMENT,

  firstname varchar (50) NOT NULL,

  lastname varchar (50) NOT NULL,

  KUNCI UTAMA (id)

) ENGINE = INNODB;

 

BUAT kontak TABEL (

  id INT,

  customer_id INT,

  info varchar (50) NOT NULL,

  ketik varchar (50) NOT NULL

) ENGINE = INNODB;

 

ALTER TABEL hubungi ADD INDEX par_ind (customer_id);

ALTER TABLE contact ADD CONSTRAINT fk_customer

KUNCI ASING (customer_id) REFERENSI pelanggan (id) DI DELETE CASCADE PADA PEMBARUAN TERBARU;

Perhatikan bahwa Anda tidak dapat mengubah kunci asing yang ada. Pertama-tama Anda harus menghapus kunci asing yang ada dengan nama simbolnya dan kemudian Anda dapat menentukan kunci asing baru seperti yang kami lakukan sebelumnya.

1 ALTER TABEL hubungi DROP ASING KUNCI fk_customer;

Kata-kata Akhir tentang Kunci Asing

Kunci asing merupakan bagian integral dari manajemen basis data yang baik. Jika Anda sekarang tertarik untuk melihat contoh penggunaannya yang lebih maju, lihat bagian Contoh Klausul Asing di sini.

Pada halaman itu adalah contoh di mana tabel “product_order” memiliki kunci asing untuk dua tabel lainnya. Satu kunci asing merujuk indeks dua kolom di tabel “Produk”. Yang lain merujuk indeks satu kolom di tabel “Pelanggan”.

Contoh kunci asing lainnya yang menarik dapat ditemukan di sini.

Tambahan: Batasan Kunci Asing

Batasan kunci asing digunakan untuk menjaga data di tabel Anda konsisten ketika menghapus (ON DELETE) atau memperbarui (ON UPDATE) data baris tabel.

Sintaks untuk definisi batasan kunci asing dalam pernyataan CREATE TABLE atau ALTER TABLE terlihat seperti ini:

1
2
3
4

[CONSTRAINT [simbol]] KUNCI ASING

[index_name] (index_column_name, …)

REFERENSI parent_table_name (index_column_name, …)

[ON DELETE reference_option] [ON UPDATE reference_option]

Bagian “CONSTRAINT [simbol]” adalah opsional dan dapat digunakan untuk menentukan nama untuk kunci asing. Ini berguna untuk menghapus kunci asing dengan nama simbol mereka dan juga untuk menghasilkan logging kesalahan yang lebih jelas.

Nilai index_name mewakili ID kunci asing dan diabaikan jika sudah ada indeks yang didefinisikan secara eksplisit pada tabel anak yang dapat mendukung kunci asing. Satu atau lebih kolom dapat digunakan untuk mengidentifikasi kunci asing secara unik.

Opsi Referensi

Digunakan oleh ON DELETE dan ON UPDATE, reference_option dapat menunjuk ke salah satu dari yang berikut:

RIAM:

Perintah ini menghapus atau memperbarui baris dari tabel induk, dan secara otomatis menghapus atau memperbarui baris yang cocok di tabel anak.

MEMBATASI:

Ini menolak operasi DELETE atau UPDATE untuk tabel induk. Menentukan RESTRICT sama dengan menghilangkan klausa ON DELETE atau ON UPDATE. Dengan kata lain, penolakan adalah tindakan default di MySQL.

TIDAK ADA TINDAKAN:

Kata kunci dari SQL standar, NO ACTION pada dasarnya setara dengan perintah RESTRICT di MySQL.

SET NULL:

Akhirnya, perintah SET NULL menghapus atau memperbarui baris dari tabel induk, dan mengatur kolom kunci asing (atau kolom) di tabel anak ke NULL. Tindakan ini dapat berguna dalam situasi di mana baris dalam tabel anak tidak boleh dihapus ketika baris tabel induk dihapus.

Sumber Foto: www.mvpcoo.com

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me