Ralat MySQL 150: Menyelesaikan masalah kekangan kunci asing semasa membuat jadual

Question

Saya cuba mencipta jadual dalam MySQL dengan 2 kekunci asing yang merujuk kekunci utama dalam 2 jadual lain, tetapi saya mendapat ralat errno: 150 dan ia tidak akan mencipta jadual.

Berikut ialah SQL untuk kesemua 3 jadual:

CREATE TABLE role_groups (
  `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
  `name` varchar(20),
  `description` varchar(200),
  PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50),
  `description` varchar(200),
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;

create table role_map (
  `role_map_id` int not null `auto_increment`,
  `role_id` int not null,
  `role_group_id` int not null,
  primary key(`role_map_id`),
  foreign key(`role_id`) references roles(`role_id`),
  foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;

Answer 1

Syarat ini mesti dipenuhi untuk mengelakkan ralat 150 apabila ALTER TABLE ADD FOREIGN KEY:

1. Jadual induk mesti wujud sebelum kunci asing boleh ditakrifkan untuk merujuknya. Anda mesti menentukan jadual dalam susunan yang betul: jadual induk dahulu, kemudian jadual anak. Jika dua jadual merujuk antara satu sama lain, anda mesti mencipta satu jadual tanpa kekangan FK, kemudian buat jadual kedua, dan kemudian gunakan ALTER TABLE untuk menambah kekangan FK pada jadual pertama.

2. Kedua-dua jadual mesti menyokong kekangan kunci asing, iaitu ENGINE=InnoDB. Enjin storan lain secara senyap mengabaikan definisi kunci asing, jadi mereka tidak mengembalikan ralat atau amaran, tetapi tidak menyimpan kekangan FK.

3. Lajur yang dirujuk dalam jadual induk mestilah lajur paling kiri kunci. Terbaik jika kunci dalam ibu bapa adalah PRIMARY KEY 或 UNIQUE KEY.

4. Takrifan FK mesti merujuk lajur PK dalam susunan yang sama seperti takrifan PK. Contohnya, jika susunan FK REFERENCES Parent(a,b,c)，则不得按 (a,c,b) mentakrifkan PK Ibu Bapa pada lajur.

5. Lajur PK dalam jadual induk mesti mempunyai jenis data yang sama dengan lajur FK dalam jadual anak. Contohnya, jika lajur PK dalam jadual induk ialah UNSIGNED，请务必为子表字段中的相应列定义 UNSIGNED.

   Pengecualian: Panjang rentetan mungkin berbeza-beza. Contohnya, VARCHAR(10) 可以引用 VARCHAR(20) dan sebaliknya.

6. Mana-mana lajur FK jenis rentetan mesti mempunyai set aksara dan susunan yang sama seperti lajur PK yang sepadan.

7. Jika sudah ada data dalam jadual anak, setiap nilai dalam lajur FK mesti sepadan dengan nilai dalam lajur PK jadual induk. Semak ini menggunakan pertanyaan seperti ini:

   SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK 
    WHERE Parent.PK IS NULL;

   Ini mesti mengembalikan sifar (0) nilai yang tidak sepadan. Jelas sekali, pertanyaan ini ialah contoh generik; anda mesti menggantikan nama jadual dan lajur.

8. Meja induk mahupun meja anak boleh jadi TEMPORARY meja.

9. Meja induk mahupun meja kanak-kanak tidak boleh dijadikan meja PARTITIONED.

10. Jika FK diisytiharkan menggunakan pilihan ON DELETE SET NULL, lajur FK mesti boleh dibatalkan.

11. Jika anda mengisytiharkan nama kekangan untuk kunci asing, nama kekangan mestilah unik merentas keseluruhan skema, bukan hanya dalam jadual di mana kekangan ditakrifkan. Dua jadual tidak boleh mempunyai kekangan sendiri dengan nama yang sama.

12. Jika terdapat mana-mana FK lain dalam jadual lain yang menunjuk ke medan yang sama yang anda cuba buat FK baharu, dan ia salah bentuk (iaitu pengumpulan berbeza), anda perlu menjadikannya konsisten terlebih dahulu. Ini mungkin disebabkan oleh perubahan masa lalu di mana SET FOREIGN_KEY_CHECKS = 0; ditakrifkan secara salah sebagai hubungan yang tidak konsisten. Lihat jawapan @andrewdotn di bawah untuk mendapatkan arahan tentang cara mengenal pasti FK yang bermasalah ini.