Cara membuat indeks kunci asing di mysql

Kunci asing adalah kendala yang dapat digunakan untuk menegakkan integritas data. Itu disusun oleh kolom (atau sekumpulan kolom) dalam tabel yang disebut tabel anak, yang merujuk ke kolom (atau sekumpulan kolom) dalam tabel yang disebut tabel induk. Jika kunci asing digunakan, MariaDB melakukan beberapa pemeriksaan untuk memastikan bahwa beberapa aturan integritas selalu diterapkan. Untuk penjelasan yang lebih lengkap, lihat Database relasional. Kunci Asing

Kunci asing hanya dapat digunakan dengan mesin penyimpanan yang mendukungnya. InnoDB default dan PBXT usang mendukung kunci asing

Tabel yang dipartisi tidak boleh berisi kunci asing, dan tidak dapat direferensikan oleh kunci asing

Sintaksis

Catatan. Sampai MariaDB 10. 4, MariaDB menerima format pintasan dengan klausa REFERENSI hanya dalam pernyataan ALTER TABLE dan CREATE TABLE, tetapi sintaks itu tidak melakukan apa-apa. Sebagai contoh

CREATE TABLE b(for_key INT REFERENCES a(not_key));

MariaDB hanya mem-parsingnya tanpa mengembalikan kesalahan atau peringatan apa pun, untuk kompatibilitas dengan DBMS lain. Namun, hanya sintaks yang dijelaskan di bawah ini yang membuat kunci asing

Dari MariaDB 10. 5, MariaDB akan mencoba menerapkan batasan tersebut. Lihat di bawah ini

Kunci asing dibuat dengan CREATE TABLE atau ALTER TABLE. Definisi harus mengikuti sintaks ini

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Klausul symbol, jika ditentukan, digunakan dalam pesan kesalahan dan harus unik dalam database

Kolom dalam tabel anak harus berupa indeks BTREE (bukan HASH, RTREE, atau FULLTEXT — lihat SHOW INDEX), atau bagian paling kiri dari indeks BTREE. Prefiks indeks tidak didukung (sehingga kolom TEXT dan BLOB tidak dapat digunakan sebagai kunci asing). Jika MariaDB secara otomatis membuat indeks untuk kunci asing (karena tidak ada dan tidak dibuat secara eksplisit), namanya akan menjadi

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Kolom yang direferensikan dalam tabel induk harus berupa indeks atau awalan indeks

Kolom kunci asing dan kolom yang direferensikan harus dari jenis yang sama, atau jenis yang serupa. Untuk tipe integer, ukuran dan tandanya juga harus sama

Kolom kunci asing dan kolom yang direferensikan dapat berupa kolom PERSISTEN. Namun, klausa ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL tidak diperbolehkan dalam kasus ini

Tabel induk dan anak harus menggunakan mesin penyimpanan yang sama, dan tidak boleh

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Kendala

Jika ada kunci asing, setiap baris dalam tabel anak harus cocok dengan baris dalam tabel induk. Beberapa baris anak dapat cocok dengan baris induk yang sama. Baris anak cocok dengan baris induk jika semua nilai kunci asing identik dengan nilai baris induk di tabel induk. Namun, jika setidaknya salah satu nilai kunci asing adalah

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

MariaDB melakukan pemeriksaan tertentu untuk menjamin bahwa integritas data ditegakkan

• Mencoba menyisipkan baris yang tidak cocok (atau memperbarui baris yang cocok dengan cara yang menjadikannya baris yang tidak cocok) di tabel anak menghasilkan kesalahan 1452 (SQLSTATE '23000')
• Ketika sebuah baris dalam tabel induk dihapus dan setidaknya ada satu baris anak, MariaDB melakukan tindakan yang bergantung pada klausa

  [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
  
  reference_option:
      RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
  

  3 dari kunci asing
• Ketika nilai dalam kolom yang direferensikan oleh kunci asing berubah dan setidaknya ada satu baris anak, MariaDB melakukan tindakan yang bergantung pada klausa

  [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
  
  reference_option:
      RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
  

  4 dari kunci asing
• Mencoba menjatuhkan tabel yang direferensikan oleh kunci asing menghasilkan kesalahan 1217 (SQLSTATE '23000')
• TABEL TRUNCATE terhadap tabel yang berisi satu atau lebih kunci asing dieksekusi sebagai DELETE tanpa WHERE, sehingga kunci asing diberlakukan untuk setiap baris

Tindakan yang diizinkan untuk

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

• [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
  
  reference_option:
      RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
  

  _7. Perubahan pada tabel induk dicegah. Pernyataan berakhir dengan kesalahan 1451 (SQLSTATE '2300'). Ini adalah perilaku default untuk

  [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
  
  reference_option:
      RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
  

  _3 dan

  [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
  
  reference_option:
      RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
  

  4

• CREATE TABLE author (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
  ) ENGINE = InnoDB;
  
  CREATE TABLE book (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT `fk_book_author`
      FOREIGN KEY (author_id) REFERENCES author (id)
      ON DELETE CASCADE
      ON UPDATE RESTRICT
  ) ENGINE = InnoDB;
  

  0. Sinonim untuk

  [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
  
  reference_option:
      RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
  

  _7

• CREATE TABLE author (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
  ) ENGINE = InnoDB;
  
  CREATE TABLE book (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT `fk_book_author`
      FOREIGN KEY (author_id) REFERENCES author (id)
      ON DELETE CASCADE
      ON UPDATE RESTRICT
  ) ENGINE = InnoDB;
  

  2. Perubahan diperbolehkan dan disebarkan di tabel anak. Misalnya, jika baris induk dihapus, baris anak juga dihapus;

• CREATE TABLE author (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
  ) ENGINE = InnoDB;
  
  CREATE TABLE book (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT `fk_book_author`
      FOREIGN KEY (author_id) REFERENCES author (id)
      ON DELETE CASCADE
      ON UPDATE RESTRICT
  ) ENGINE = InnoDB;
  

  3. Perubahan diperbolehkan, dan kolom kunci asing baris turunan disetel ke

  [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
  
  reference_option:
      RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
  

  2

• CREATE TABLE author (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
  ) ENGINE = InnoDB;
  
  CREATE TABLE book (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT `fk_book_author`
      FOREIGN KEY (author_id) REFERENCES author (id)
      ON DELETE CASCADE
      ON UPDATE RESTRICT
  ) ENGINE = InnoDB;
  

  5. Hanya bekerja dengan PBXT. Mirip dengan

  CREATE TABLE author (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
  ) ENGINE = InnoDB;
  
  CREATE TABLE book (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT `fk_book_author`
      FOREIGN KEY (author_id) REFERENCES author (id)
      ON DELETE CASCADE
      ON UPDATE RESTRICT
  ) ENGINE = InnoDB;
  

  _3, tetapi kolom kunci asing disetel ke nilai defaultnya. Jika nilai default tidak ada, kesalahan dihasilkan

Operasi hapus atau perbarui yang dipicu oleh kunci asing tidak mengaktifkan pemicu dan tidak dihitung dalam variabel dan status

Batasan kunci asing dapat dinonaktifkan dengan menyetel variabel sistem server ke 0. Ini mempercepat penyisipan data dalam jumlah besar

Tabel Skema Informasi

CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

Tabel Skema Informasi khusus InnoDB juga berisi informasi tentang kunci asing InnoDB. Informasi kunci asing disimpan di

CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

Cara yang paling mudah dibaca manusia untuk mendapatkan informasi tentang kunci asing tabel terkadang adalah pernyataan

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

Keterbatasan

Kunci asing memiliki batasan berikut di MariaDB

• Saat ini, kunci asing hanya didukung oleh InnoDB
• Tidak dapat digunakan dengan tampilan
• Tindakan

  CREATE TABLE author (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
  ) ENGINE = InnoDB;
  
  CREATE TABLE book (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT `fk_book_author`
      FOREIGN KEY (author_id) REFERENCES author (id)
      ON DELETE CASCADE
      ON UPDATE RESTRICT
  ) ENGINE = InnoDB;
  

  _5 tidak didukung
• Tindakan kunci asing tidak mengaktifkan pemicu
• Jika ON UPDATE CASCADE berulang untuk memperbarui tabel yang sama yang telah diperbarui sebelumnya selama kaskade, tindakannya seperti RESTRICT

Contoh

Mari kita lihat contohnya. Kami akan membuat tabel

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

Sekarang, jika kami mencoba memasukkan buku dengan penulis yang tidak ada, kami akan mendapatkan kesalahan

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

Kesalahannya sangat deskriptif

Sekarang, mari kita coba memasukkan dua penulis dan buku mereka dengan benar

INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
  ('The call of Cthulhu', LAST_INSERT_ID()),
  ('The colour out of space', LAST_INSERT_ID());

Itu berhasil

Sekarang, mari kita hapus penulis kedua. Saat kami membuat kunci asing, kami menentukan

INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
  ('The call of Cthulhu', LAST_INSERT_ID()),
  ('The colour out of space', LAST_INSERT_ID());

DELETE FROM author WHERE name = 'H.P. Lovecraft';

SELECT * FROM book;
+----+--------------+-----------+
| id | title        | author_id |
+----+--------------+-----------+
|  3 | Necronomicon |         1 |
+----+--------------+-----------+

Kami juga menentukan

INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
  ('The call of Cthulhu', LAST_INSERT_ID()),
  ('The colour out of space', LAST_INSERT_ID());

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

Bagaimana cara membuat indeks kunci asing?

Bisakah kita MEMBUAT INDEX pada kolom kunci asing di MySQL?

Bagaimana cara membuat indeks di MySQL?

Apakah kunci asing diindeks secara default di MySQL?