Belajar MySQL Foreign Key

Dalam tutorial ini, 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.

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.
 
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 .
 
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 UPDATE action 



ON DELETE 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

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 ;



   cat_id int not null auto_increment primary key ,



CREATE TABLE categories (



   cat_description text



   cat_name varchar ( 255 ) not null ,

) ENGINE = InnoDB ;
 


   prd_id int not null auto_increment primary key ,



CREATE TABLE products (



   prd_name varchar ( 355 ) not null ,



   REFERENCES categories ( cat_id )



   prd_price decimal ,



   cat_id int not null ,



   FOREIGN KEY fk_cat ( cat_id )



) ENGINE = InnoDB ; 



   ON UPDATE CASCADE



   ON DELETE RESTRICT

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 UPDATE action 



ON DELETE 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 ;
 


ADD COLUMN vdr_id int not null AFTER cat_id ; 



ALTER TABLE products

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 UPDATE CASCADE ; 



ON DELETE NO ACTION
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 ,



   cat_id int ( 11 ) NOT NULL ,



   prd_price decimal ( 10 , 0 ) DEFAULT NULL ,

vdr_id int ( 11 ) NOT NULL ,


 



   PRIMARY KEY ( prd_id ) ,

KEY fk_cat ( cat_id ) ,


   KEY fk_vendor ( vdr_id ) ,



   ON DELETE NO ACTION



   CONSTRAINT products_ibfk_2

FOREIGN KEY ( vdr_id )


   REFERENCES vendors ( vdr_id )

ON UPDATE CASCADE ,
 


) ENGINE = InnoDB ; 



   CONSTRAINT products_ibfk_1

FOREIGN KEY ( cat_id )


   REFERENCES categories ( cat_id )



   ON UPDATE CASCADE

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

Comments

Post a Comment