MySQL Tworzenie tabel z kluczami obcymi dającymi errno: 150

Próbuję utworzyć tabelę w MySQL z dwoma kluczami obcymi, które odwołują się do kluczy podstawowych w 2 innych tabelach, ale otrzymuję błąd errno: 150 i nie utworzy tabeli.

Oto SQL dla wszystkich 3 tabel:

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;

Każda pomoc byłaby bardzo mile widziana.

Miałem ten sam problem z ALTER TABLE ADD FOREIGN KEY.

Po godzinie stwierdziłem, że te warunki muszą być spełnione, aby nie pojawił się błąd 150:

1. Tabela nadrzędna musi istnieć, zanim zdefiniujesz klucz obcy, aby się do niej odwoływać. Musisz zdefiniować tabele w odpowiedniej kolejności: najpierw tabela nadrzędna, a następnie tabela podrzędna. Jeśli obie tabele odwołują się do siebie, musisz utworzyć jedną tabelę bez ograniczeń FK, a następnie utworzyć drugą tabelę, a następnie dodać ograniczenie FK do pierwszej tabeli za pomocą ALTER TABLE.

2. Obie tabele muszą obsługiwać ograniczenia klucza obcego, tj ENGINE=InnoDB. Inne silniki magazynu dyskretnie ignorują definicje kluczy obcych, więc nie zwracają żadnych błędów ani ostrzeżeń, ale ograniczenie FK nie jest zapisywane.

3. Odwoływane kolumny w tabeli nadrzędnej muszą być skrajnymi lewymi kolumnami klucza. Najlepiej, jeśli klucz w Rodzinie to PRIMARY KEYlub UNIQUE KEY.

4. Definicja FK musi odnosić się do kolumny (kolumn) PK w tej samej kolejności, co definicja PK. Na przykład, jeśli FK, REFERENCES Parent(a,b,c)to PK elementu nadrzędnego nie może być zdefiniowany w kolumnach w kolejności (a,c,b).

5. Kolumny PK w tabeli nadrzędnej muszą mieć ten sam typ danych, co kolumny FK w tabeli podrzędnej. Na przykład, jeśli kolumna PK w tabeli nadrzędnej to UNSIGNED, należy zdefiniować UNSIGNEDodpowiednią kolumnę w polu tabeli podrzędnej.

   Wyjątek: długość sznurków może być inna. Na przykład VARCHAR(10)może odwoływać się VARCHAR(20)lub odwrotnie.

6. Każda kolumna (kolumny) typu łańcuchowego musi mieć ten sam zestaw znaków i sortowanie, jak odpowiadające jej kolumny PK.

7. Jeśli w tabeli podrzędnej znajdują się już dane, każda wartość w kolumnach FK musi odpowiadać wartości w kolumnach PK tabeli nadrzędnej. Sprawdź to za pomocą zapytania takiego jak:

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

   Musi zwrócić zero (0) niedopasowanych wartości. Oczywiście to zapytanie jest ogólnym przykładem; musisz podstawić nazwy tabel i nazwy kolumn.

8. Ani tabela nadrzędna, ani podrzędna nie mogą być TEMPORARYtabelami.

9. Ani tabela nadrzędna, ani podrzędna nie mogą być PARTITIONEDtabelami.

10. Jeśli deklarujesz FK z ON DELETE SET NULLopcją, kolumny FK muszą mieć wartość null.

11. Jeśli zadeklarujesz nazwę ograniczenia dla klucza obcego, nazwa ograniczenia musi być unikalna w całym schemacie, a nie tylko w tabeli, w której zdefiniowane jest ograniczenie. Dwie tabele mogą nie mieć własnego ograniczenia o tej samej nazwie.

12. Jeśli w innych tabelach istnieją inne FK, które wskazują to samo pole, dla którego próbujesz utworzyć nowy FK, i są one zniekształcone (tj. Inne sortowanie), należy je najpierw ujednolicić. Może to być wynikiem wcześniejszych zmian, w przypadku których SET FOREIGN_KEY_CHECKS = 0;został użyty z niespójną relacją zdefiniowaną przez pomyłkę. Zobacz odpowiedź @ andrewdotn poniżej, aby uzyskać instrukcje, jak zidentyfikować te FK.

Mam nadzieję że to pomoże.

Ogólny komunikat MySQL „errno 150” „ oznacza, że ​​ograniczenie klucza obcego nie zostało poprawnie utworzone ”. Jak zapewne już wiesz, czytając tę ​​stronę, ogólny komunikat o błędzie „errno: 150” jest naprawdę nieprzydatny. Jednak:

Możesz uzyskać rzeczywisty komunikat o błędzie, uruchamiając, SHOW ENGINE INNODB STATUS;a następnie szukając LATEST FOREIGN KEY ERRORw danych wyjściowych.

Na przykład ta próba utworzenia ograniczenia klucza obcego:

CREATE TABLE t1
(id INTEGER);

CREATE TABLE t2
(t1_id INTEGER,
 CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));

nie powiedzie się z błędem Can't create table 'test.t2' (errno: 150). To nie mówi nikomu nic użytecznego poza tym, że jest to problem z kluczem obcym. Ale biegnij, SHOW ENGINE INNODB STATUS;a powie:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

Mówi, że problem polega na tym, że nie może znaleźć indeksu. SHOW INDEX FROM t1pokazuje, że nie ma żadnych indeksów dla tabeli t1. Napraw to, powiedzmy, definiując klucz podstawowy t1, a ograniczenie klucza obcego zostanie utworzone pomyślnie.

Upewnij się, że właściwości dwóch pól, które próbujesz połączyć z ograniczeniem, są dokładnie takie same.

Często właściwość „unsigned” w kolumnie ID może Cię zaskoczyć.

ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;

Jaki jest aktualny stan Twojej bazy danych po uruchomieniu tego skryptu? Czy jest całkowicie pusty? Twój SQL działa dla mnie dobrze podczas tworzenia bazy danych od zera, ale errno 150 zwykle wiąże się z usuwaniem i odtwarzaniem tabel, które są częścią klucza obcego. Mam wrażenie, że nie pracujesz ze 100% świeżą i nową bazą danych.

Jeśli błędnie wykonujesz "source" -ing swojego pliku SQL, powinieneś być w stanie uruchomić polecenie "SHOW ENGINE INNODB STATUS" z wiersza poleceń MySQL bezpośrednio po poleceniu "source", aby zobaczyć bardziej szczegółowe informacje o błędzie.

Możesz również sprawdzić wpis ręczny:

Jeśli ponownie utworzysz usuniętą tabelę, musi ona mieć definicję zgodną z powiązanymi z nią ograniczeniami klucza obcego. Musi mieć odpowiednie nazwy i typy kolumn oraz musi mieć indeksy w przywoływanych kluczach, jak wspomniano wcześniej. Jeśli te kryteria nie są spełnione, MySQL zwraca numer błędu 1005 i odsyła do błędu 150 w komunikacie o błędzie. Jeśli MySQL zgłasza błąd numer 1005 z instrukcji CREATE TABLE, a komunikat o błędzie odnosi się do błędu 150, utworzenie tabeli nie powiodło się, ponieważ ograniczenie klucza obcego nie zostało poprawnie utworzone.

- Podręcznik referencyjny MySQL 5.1 .

Dla osób, które przeglądają ten wątek z tym samym problemem:

Istnieje wiele powodów, dla których pojawiają się takie błędy. Aby uzyskać dość pełną listę przyczyn i rozwiązań błędów kluczy obcych w MySQL (w tym omawianych tutaj), sprawdź ten link:

Błędy klucza obcego MySQL i Errno 150

Dla innych, którzy znajdą ten wpis SO za pośrednictwem Google: upewnij się, że nie próbujesz wykonać akcji SET NULL na kolumnie klucza obcego (która ma być) zdefiniowanej jako „NOT NULL”. To powodowało wielką frustrację, dopóki nie przypomniałem sobie, żeby zrobić CHECK ENGINE INNODB STATUS.

Zdecydowanie tak nie jest, ale uznałem ten błąd za dość powszechny i ​​nieoczywisty. Celem a FOREIGN KEYnie mogło być PRIMARY KEY. Odpowiedź, która mi się przydała, to:

KLUCZ OBCY zawsze musi być wskazany na prawdziwe pole PRIMARY KEY innej tabeli.

CREATE TABLE users(
   id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40));

CREATE TABLE userroles(
   id INT AUTO_INCREMENT PRIMARY KEY,
   user_id INT NOT NULL,
   FOREIGN KEY(user_id) REFERENCES users(id));

Jak wskazał @andrewdotn, najlepszym sposobem jest zobaczenie szczegółowego błędu ( SHOW ENGINE INNODB STATUS;) zamiast tylko kodu błędu.

Jednym z powodów może być to, że indeks o tej samej nazwie już istnieje, może znajdować się w innej tabeli. W ramach praktyki zalecam przedrostki nazwy tabeli przed nazwą indeksu, aby uniknąć takich kolizji. np. zamiast idx_userIdużywać idx_userActionMapping_userId.

Najpierw upewnij się, że

1. korzystasz z tabel InnoDB.
2. pole KLUCZ OBCY ma ten sam typ i długość (!) jak pole źródłowe.

Miałem ten sam problem i naprawiłem go. Miałem bez znaku INT dla jednego pola i tylko liczbę całkowitą dla drugiego pola.

Pomocna wskazówka, użyj SHOW WARNINGS;po wypróbowaniu CREATEzapytania, a otrzymasz błąd, a także bardziej szczegółowe ostrzeżenie:

    ---------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                 |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+
| Warning |  150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error   | 1005 | Can't create table 'exampleTable' (errno:150)                                                                                                                                                                           |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+

W takim razie czas odtworzyć mój stół!

Zwykle dzieje się tak, gdy próbujesz przenieść plik źródłowy do istniejącej bazy danych. Najpierw usuń wszystkie tabele (lub samą bazę danych). A potem plik źródłowy z SET foreign_key_checks = 0;na początku i SET foreign_key_checks = 1;na końcu.

Znalazłem inny powód, dla którego to się nie udaje ... nazwy tabel uwzględniające wielkość liter.

Dla tej definicji tabeli

CREATE TABLE user (
  userId int PRIMARY KEY AUTO_INCREMENT,
  username varchar(30) NOT NULL
) ENGINE=InnoDB;

Ta definicja tabeli działa

CREATE TABLE product (
  id int PRIMARY KEY AUTO_INCREMENT,
  userId int,
  FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
) ENGINE=InnoDB;

podczas gdy ten zawodzi

CREATE TABLE product (
  id int PRIMARY KEY AUTO_INCREMENT,
  userId int,
  FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
) ENGINE=InnoDB;

Fakt, że działał w systemie Windows i nie działał w systemie Unix, zajął mi kilka godzin, aby się zorientować. Mam nadzieję, że to pomoże komuś innemu.

MySQL Workbench 6.3 dla Mac OS.

Problem: errno 150 w tabeli X podczas próby wykonania inżynierii naprzód na diagramie bazy danych, 20 z 21 powiodło się, 1 nie powiodło się. Jeśli skasowane elementy w tabeli X zostały usunięte, błąd został przeniesiony do innej tabeli, która wcześniej nie kończyła się niepowodzeniem.

Zmieniono silnik wszystkich tabel na myISAM i działał dobrze.

Warto również sprawdzić, czy przypadkowo nie operujesz na złej bazie danych. Ten błąd wystąpi, jeśli tabela obca nie istnieje. Dlaczego MySQL musi być tak tajemniczy?

Upewnij się, że klucze obce nie są wymienione jako unikalne w nadrzędnym. Miałem ten sam problem i rozwiązałem go, określając go jako nie jedyny.

W moim przypadku było to spowodowane tym, że pole będące polem klucza obcego miało zbyt długą nazwę, tj. foreign key (some_other_table_with_long_name_id). Spróbuj coś krócej. Komunikat o błędzie jest w tym przypadku nieco mylący.

Ponadto, jak wspomniał wcześniej @Jon - definicje pól muszą być takie same (uwaga na unsignedpodtyp).

(Nuty boczne są zbyt duże na komentarz)

Nie ma potrzeby AUTO_INCREMENTpodawania identyfikatora w tabeli mapowania; pozbądź się tego.

Zmienić PRIMARY KEYsię (role_id, role_group_id)(w dowolnej kolejności). To przyspieszy dostęp.

Ponieważ prawdopodobnie chcesz odwzorować oba kierunki, dodaj także INDEXte dwie kolumny w odwrotnej kolejności. (Nie ma potrzeby tego robić UNIQUE).

Więcej wskazówek: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Gdy ograniczenie klucza obcego jest na podstawie varchartypu, wówczas oprócz wykazu udostępnionego przezmarv-el w kolumnie docelowej musi mieć UNIQUE.

wykonaj poniższą linię przed utworzeniem tabeli: SET FOREIGN_KEY_CHECKS = 0;

Opcja FOREIGN_KEY_CHECKS określa, czy sprawdzać ograniczenia klucza obcego dla tabel InnoDB.

- Określ, aby sprawdzić ograniczenia klucza obcego (jest to ustawienie domyślne)

SET FOREIGN_KEY_CHECKS = 1;

- Nie sprawdzaj ograniczeń klucza obcego

SET FOREIGN_KEY_CHECKS = 0;

Kiedy używać: tymczasowe wyłączanie ograniczeń referencyjnych (ustaw FOREIGN_KEY_CHECKS na 0) jest przydatne, gdy musisz ponownie utworzyć tabele i załadować dane w dowolnej kolejności nadrzędny-podrzędny

Napotkałem ten sam problem, ale sprawdzam, czy nie mam tabeli nadrzędnej. Po prostu edytuję migrację rodzica przed migracją dziecka. Po prostu to zrób.