(7) MySQL Foreign Key

Ringkasan: dalam tutorial ini johanandyagasiblogspotcom, Anda akan belajar tentang MySQL kunci asing dan cara membuat, menambah, dan drop batasan kunci asing di MySQL.

Pengenalan MySQL kunci asing

Kunci asing adalah bidang dalam tabel yang cocok dengan bidang meja lain. Sebuah asing tempat kendala utama pada data dalam tabel terkait, yang memungkinkan MySQL untuk menjaga integritas referensial.

Mari kita lihat diagram database berikut di database contoh .

MySQL Foreign Key - Customers & Orders Tables

johanandyagasiblogspotcom Kami memiliki dua tabel: customers dan orders . Setiap pelanggan memiliki nol atau lebih perintah dan setiap pesanan milik hanya satu pelanggan. Hubungan antara customers meja dan orders meja adalah satu-ke-banyak, dan itu didirikan oleh kunci asing dalam orders tabel ditentukan oleh customerNumber lapangan. The customerNumber lapangan di orders tabel berkaitan dengan customerNumber bidang kunci utama dalam customers tabel.

The customers tabel disebut tabel induk atau meja direferensikan, dan orders tabel dikenal sebagai tabel anak atau meja referensi.

Kunci asing tidak hanya satu kolom, tetapi juga satu set kolom. Kolom dalam tabel anak sering merujuk pada kolom kunci utama dalam tabel induk.

johanandyagasiblogspotcom Sebuah meja mungkin memiliki lebih dari satu kunci asing, dan masing-masing kunci asing di tabel anak dapat memiliki tabel induk yang berbeda.

Sebuah baris dalam tabel anak harus mengandung nilai-nilai yang ada di induk meja misalnya, setiap record ketertiban di orders tabel harus memiliki customerNumber yang ada di customers tabel. Beberapa perintah dapat merujuk kepada pelanggan yang sama karena hubungan ini disebut satu (pelanggan) ke banyak (order), atau satu-ke-banyak.

Kadang-kadang, anak dan orang tua meja adalah meja yang sama. Kunci asing mengacu kembali ke primary key dari tabel misalnya, berikut employees tabel:

The reportTo kolom kunci asing yang mengacu pada employeeNumber kolom yang merupakan kunci utama dari employees meja untuk mencerminkan struktur pelaporan antara karyawan yaitu, setiap laporan karyawan untuk antera karyawan dan karyawan dapat memiliki nol atau lebih laporan langsung.

The reportTo kunci asing ini juga dikenal sebagai kunci asing rekursif atau diri-referensi.

johanandyagasiblogspotcom Kunci asing menegakkan integritas referensial yang membantu Anda menjaga konsistensi dan integritas data otomatis. Misalnya, Anda tidak dapat membuat agar pelanggan tidak ada.

Selain itu, Anda dapat mengatur kaskade aksi delete untuk customerNumber kunci asing sehingga ketika Anda menghapus pelanggan di customers meja, semua perintah yang berhubungan dengan pelanggan juga dihapus. Hal ini menghemat waktu Anda dan upaya menggunakan beberapa pernyataan DELETE atau DELETE BERGABUNG pernyataan .

johanandyagasiblogspotcom Sama seperti penghapusan, Anda juga dapat menentukan kaskade pada tindakan pembaruan untuk customerNumber kunci asing untuk melakukan update tabel silang tanpa menggunakan beberapa UPDATE pernyataan atau UPDATE BERGABUNG pernyataan .

Dalam MySQL, InnoDB storage engine mendukung kunci asing sehingga Anda harus membuat tabel InnoDB untuk menggunakan batasan kunci asing.

MySQL membuat tabel kunci asing

MySQL membuat sintaks kunci asing

Sintaks berikut menggambarkan bagaimana untuk menentukan kunci asing dalam tabel anak di CREATE TABLE pernyataan.

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name ( columns )
REFERENCES parent_table ( columns )
ON DELETE action
ON UPDATE action

Mari kita memeriksa sintaks secara lebih rinci:

The CONSTRAINT klausul memungkinkan Anda untuk menentukan nama kendala untuk batasan kunci asing. Jika Anda menghilangkan itu, MySQL akan menghasilkan nama otomatis.
The FOREIGN KEY clause menentukan kolom dalam tabel anak yang merujuk pada kolom kunci utama dalam tabel induk. Anda dapat menempatkan sebuah nama kunci asing setelah FOREIGN KEY clause atau tinggalkan untuk membiarkan MySQL untuk membuat nama untuk Anda. Perhatikan bahwa MySQL secara otomatis membuat indeks dengan foreign_key_name nama.
The REFERENCES klausul menentukan tabel induk dan kolom untuk yang kolom di tabel anak merujuk. Jumlah kolom dalam tabel anak dan tabel induk ditentukan dalam FOREIGN KEY dan REFERENCES harus sama.
The ON DELETE klausa memungkinkan Anda untuk menentukan apa yang terjadi pada catatan dalam tabel anak ketika catatan dalam tabel induk dihapus. Jika Anda menghilangkan ON DELETE klausa dan menghapus catatan dalam tabel induk yang memiliki catatan dalam tabel anak merujuk, MySQL akan menolak penghapusan. Selain itu, MySQL juga menyediakan Anda dengan tindakan sehingga Anda dapat memiliki pilihan lain seperti ON DELETE CASCADE yang memungkinkan MySQL untuk menghapus catatan dalam tabel anak yang merujuk ke rekor dalam tabel induk ketika catatan dalam tabel induk dihapus. Jika Anda tidak ingin catatan terkait di tabel anak yang akan dihapus, Anda menggunakan ON DELETE SET NULL tindakan sebaliknya. MySQL akan menetapkan nilai-nilai kolom kunci asing dalam tabel anak untuk NULL ketika catatan dalam tabel induk dihapus, dengan syarat bahwa kolom kunci asing dalam tabel anak harus menerima NULL nilai. Perhatikan bahwa jika Anda menggunakan ON DELETE NO ACTION atau ON DELETE RESTRICT tindakan, MySQL akan menolak penghapusan.
The ON UPDATE klausa memungkinkan Anda untuk menentukan apa yang terjadi pada baris dalam tabel anak ketika baris dalam tabel induk diperbarui. Anda dapat menghilangkan ON UPDATE klausa untuk membiarkan MySQL untuk menolak pembaruan apapun untuk baris dalam tabel anak ketika baris dalam tabel induk diperbarui. The ON UPDATE CASCADE tindakan memungkinkan Anda untuk melakukan update tabel silang, dan ON UPDATE SET NULL tindakan ulang nilai-nilai dalam baris dalam tabel anak untuk NULL nilai ketika baris dalam tabel induk diperbarui. The ON UPDATE NO ACTION atau UPDATE RESTRICT tindakan menolak pembaruan.

MySQL membuat tabel contoh kunci asing

johanandyagasiblogspotcom Contoh berikut membuat dbdemo database dan dua tabel: categories dan products . Setiap kategori memiliki satu atau lebih produk dan setiap produk milik satu kategori. The cat_id lapangan di products tabel didefinisikan sebagai kunci asing dengan UPDATE ON CASCADE dan DELETE ON RESTRICT tindakan.

CREATE DATABASE IF NOT EXISTS dbdemo ;
 
USE dbdemo ;
 
CREATE TABLE categories (
   cat_id int not null auto_increment primary key ,
   cat_name varchar ( 255 ) not null ,
   cat_description text
) ENGINE = InnoDB ;
 
CREATE TABLE products (
   prd_id int not null auto_increment primary key ,
   prd_name varchar ( 355 ) not null ,
   prd_price decimal ,
   cat_id int not null ,
   FOREIGN KEY fk_cat ( cat_id )
   REFERENCES categories ( cat_id )
   ON UPDATE CASCADE
   ON DELETE RESTRICT
) ENGINE = InnoDB ;

MySQL menambahkan kunci asing

MySQL menambahkan sintaks kunci asing

Untuk menambahkan kunci asing untuk tabel yang ada, Anda menggunakan ALTER TABLE pernyataan dengan sintaks definisi kunci asing di atas:

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name ( columns )
REFERENCES parent_table ( columns )
ON DELETE action
ON UPDATE action

MySQL menambahkan contoh kunci asing

Sekarang, mari kita tambahkan tabel bernama baru vendors dan mengubah products tabel untuk memasukkan bidang penjual id:

USE dbdemo ;
 
CREATE TABLE vendors (
     vdr_id int not null auto_increment primary key ,
     vdr_name varchar ( 255 )
) ENGINE = InnoDB ;
 
ALTER TABLE products
ADD COLUMN vdr_id int not null AFTER cat_id ;

Untuk menambahkan kunci asing ke products meja, Anda menggunakan pernyataan berikut:

ALTER TABLE products
ADD FOREIGN KEY fk_vendor ( vdr_id )
REFERENCES vendors ( vdr_id )
ON DELETE NO ACTION
ON UPDATE CASCADE ;

Sekarang, products meja memiliki dua kunci asing, satu mengacu pada categories meja dan lain mengacu pada vendors meja.

Penurunan MySQL kunci asing

Anda juga menggunakan ALTER TABLE pernyataan untuk menjatuhkan kunci asing sebagai pernyataan berikut:

 ALTER TABLE table_name
DROP FOREIGN KEY constraint_name

Dalam pernyataan di atas:

Pertama, Anda menentukan nama tabel dari mana Anda ingin menghapus kunci asing.
Kedua, Anda menempatkan nama kendala setelah DROP FOREIGN KEY clause.

johanandyagasiblogspotcom Perhatikan bahwa constraint_name adalah nama dari kendala yang ditentukan saat membuat atau menambahkan kunci asing ke meja. Jika Anda menghilangkan itu, MySQL menghasilkan nama kendala untuk Anda.

Untuk mendapatkan nama kendala yang dihasilkan dari meja, Anda menggunakan SHOW CREATE TABLE pernyataan sebagai berikut:

 SHOW CREATE TABLE table_name

Misalnya, untuk melihat kunci asing dari products meja, Anda menggunakan pernyataan berikut:

SHOW CREATE TABLE products

Berikut ini adalah output dari pernyataan:

 CREATE TABLE products (
   prd_id int ( 11 ) NOT NULL AUTO_INCREMENT ,
   prd_name varchar ( 355 ) NOT NULL ,
   prd_price decimal ( 10 , 0 ) DEFAULT NULL ,
   cat_id int ( 11 ) NOT NULL ,
   vdr_id int ( 11 ) NOT NULL ,
   PRIMARY KEY ( prd_id ) ,
   KEY fk_cat ( cat_id ) ,
   KEY fk_vendor ( vdr_id ) ,
 
   CONSTRAINT products_ibfk_2
   FOREIGN KEY ( vdr_id )
   REFERENCES vendors ( vdr_id )
   ON DELETE NO ACTION
   ON UPDATE CASCADE ,
 
   CONSTRAINT products_ibfk_1
   FOREIGN KEY ( cat_id )
   REFERENCES categories ( cat_id )
   ON UPDATE CASCADE
) ENGINE = InnoDB ;

The products meja memiliki dua batasan kunci asing: products_ibfk_1 dan products_ibfk_2 .

Anda dapat drop kunci asing dari products meja dengan menggunakan pernyataan berikut:

 ALTER TABLE products
DROP FOREIGN KEY products_ibfk_1 ;
 
ALTER TABLE products
DROP FOREIGN KEY products_ibfk_2 ;

MySQL menonaktifkan pemeriksaan kunci asing

Kadang-kadang, hal ini sangat berguna untuk menonaktifkan pemeriksaan kunci asing misalnya, ketika Anda memuat data ke dalam tabel yang memiliki kunci asing. Jika Anda tidak menonaktifkan pemeriksaan kunci asing, Anda harus memuat data ke dalam urutan yang tepat yaitu, Anda harus memuat data ke dalam tabel induk pertama dan kemudian tabel anak, yang dapat membosankan. Namun jika Anda menonaktifkan pemeriksaan kunci asing, Anda dapat memuat data ke dalam perintah.

Contoh lain adalah bahwa, kecuali jika Anda menonaktifkan pemeriksaan kunci asing, Anda tidak dapat menjatuhkan meja yang direferensikan oleh batasan kunci asing. Ketika Anda drop tabel, kendala yang ditetapkan untuk meja juga dihapus.

Untuk menonaktifkan pemeriksaan kunci asing, Anda menggunakan pernyataan berikut:

 SET foreign_key_checks = 0

Dan tentu saja, Anda dapat mengaktifkannya dengan menggunakan pernyataan di bawah ini:

SET foreign_key_checks = 1

Dalam tutorial ini johanandyagasiblogspotcom, kita telah membahas banyak tentang MySQL kunci asing. Kami juga memperkenalkan Anda untuk beberapa pernyataan yang sangat berguna yang memungkinkan Anda untuk mengelola kunci asing secara efektif dalam MySQL.