Я пытаюсь создать таблицу в MySQL с двумя внешними ключами, которые ссылаются на первичные ключи в 2 других таблицах, но я получаю ошибку errno: 150, и она не будет создавать таблицу.
Вот SQL для всех 3 таблиц:
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;
Любая помощь будет принята с благодарностью.
У меня была такая же проблема с ALTER TABLE ADD FOREIGN KEY
,
Через час я обнаружил, что эти условия должны быть выполнены, чтобы не получить ошибку 150:
Родительская таблица должна существовать до того, как вы определите внешний ключ для ссылки на него. Вы должны определить таблицы в правильном порядке: сначала родительская таблица, затем дочерняя таблица. Если обе таблицы ссылаются друг на друга, необходимо создать одну таблицу без ограничений FK, затем создать вторую таблицу, а затем добавить ограничение FK в первую таблицу с помощью ALTER TABLE
,
Обе таблицы должны поддерживать ограничения внешнего ключа, т.е. ENGINE=InnoDB
, Другие механизмы хранения молча игнорируют определения внешних ключей, поэтому они не возвращают ошибок или предупреждений, но ограничение FK не сохраняется.
Столбцы со ссылками в родительской таблице должны быть крайними левыми столбцами ключа. Лучше всего, если ключ в родительском PRIMARY KEY
или же UNIQUE KEY
,
Определение FK должно ссылаться на столбцы PK в том же порядке, что и определение PK. Например, если FK REFERENCES Parent(a,b,c)
тогда PK Родителя не должен быть определен в столбцах в порядке (a,c,b)
,
Столбцы PK в родительской таблице должны иметь тот же тип данных, что и столбцы FK в дочерней таблице. Например, если столбец PK в родительской таблице UNSIGNED
, обязательно определите UNSIGNED
для соответствующего столбца в поле дочерней таблицы.
Исключение: длина строк может быть разной. Например, VARCHAR(10)
может ссылаться VARCHAR(20)
или наоборот.
Любые столбцы столбца типа FK должны иметь тот же набор символов и сопоставление, что и соответствующие столбцы PK.
Если в дочерней таблице уже есть данные, каждое значение в столбцах столбцов FK должно совпадать со значением в столбцах столбцов PK родительской таблицы. Проверьте это с помощью запроса:
SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK
WHERE Parent.PK IS NULL;
Это должно вернуть ноль (0) несопоставленных значений. Очевидно, этот запрос является общим примером; Вы должны заменить имена таблиц и столбцов.
Ни родительская таблица, ни дочерняя таблица не могут быть TEMPORARY
Таблица.
Ни родительская таблица, ни дочерняя таблица не могут быть PARTITIONED
Таблица.
Если вы объявляете ФК с ON DELETE SET NULL
опцию, то столбцы FK должны быть обнуляемыми.
Если вы объявляете имя ограничения для внешнего ключа, имя ограничения должно быть уникальным во всей схеме, а не только в таблице, в которой определено ограничение. Две таблицы могут не иметь собственных ограничений с одинаковыми именами.
Надеюсь это поможет.
Общее сообщение MySQL «errno 150»означает, что ограничение внешнего ключа было сформировано неправильно.«Как вы, вероятно, уже знаете, читаете ли вы эту страницу, общее сообщение об ошибке« errno: 150 »действительно бесполезно. Тем не мение:
Вы можете получить фактический сообщение об ошибке при запуске SHOW ENGINE INNODB STATUS;
а потом ищу LATEST FOREIGN KEY ERROR
на выходе.
Например, эта попытка создать ограничение внешнего ключа:
CREATE TABLE t1
(id INTEGER);
CREATE TABLE t2
(t1_id INTEGER,
CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));
не удается с ошибкой Can't create table 'test.t2' (errno: 150)
, Это никому не говорит ничего полезного, кроме того, что это проблема внешнего ключа. Но беги SHOW ENGINE INNODB STATUS;
и он скажет:
------------------------
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.
Он говорит, что проблема в том, что он не может найти индекс. SHOW INDEX FROM t1
показывает, что для таблицы нет никаких индексов t1
, Исправьте это, скажем, определив первичный ключ на t1
и ограничение внешнего ключа будет успешно создано.
Убедитесь, что свойства двух полей, которые вы пытаетесь связать с ограничением, в точности совпадают.
Часто свойство unsigned в столбце идентификаторов вас поймает.
ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;
Каково текущее состояние вашей базы данных, когда вы запускаете этот скрипт? Это полностью пусто? Ваш SQL отлично работает для меня при создании базы данных с нуля, но errno 150 обычно имеет отношение к удалению & воссоздание таблиц, которые являются частью внешнего ключа. У меня такое ощущение, что вы не работаете со 100% новой и новой базой данных.
Если вы ошибаетесь, когда «исходный» ваш файл SQL, вы сможете запустить команду «SHOW ENGINE INNODB STATUS» из подсказки MySQL сразу после команды «source», чтобы увидеть более подробную информацию об ошибке.
Вы можете проверить ручной ввод тоже:
Если вы воссоздаете таблицу, которая была удалена, у нее должно быть определение, соответствующее ограничениям внешнего ключа, ссылающимся на нее. У него должны быть правильные имена и типы столбцов, и у него должны быть индексы на ссылочных ключах, как указано ранее. Если они не удовлетворены, MySQL возвращает номер ошибки 1005 и ссылается на ошибку 150 в сообщении об ошибке. Если MySQL сообщает об ошибке 1005 из оператора CREATE TABLE, и сообщение об ошибке ссылается на ошибку 150, создание таблицы завершилось неудачно, поскольку ограничение внешнего ключа было сформировано неправильно.
Для людей, которые просматривают эту тему с той же проблемой:
Есть много причин для получения ошибок, подобных этой. Для довольно полного списка причин и решений ошибок внешнего ключа в MySQL (включая обсужденные здесь), проверьте эту ссылку:
Для других, которые находят эту запись SO через Google: убедитесь, что вы не пытаетесь выполнить действие SET NULL для столбца с внешним ключом (который должен быть), определенного как «NOT NULL». Это вызвало большое разочарование, пока я не вспомнил, что сделал CHECK ENGINE INNODB STATUS.
Определенно это не так, но я нашел эту ошибку довольно распространенной и неочевидной. Цель FOREIGN KEY
может быть не PRIMARY KEY
, Ответ, который мне пригодится, таков:
FOREIGN KEY всегда должен указывать на истинное поле PRIMARY KEY другой таблицы.
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));
Как указал @andrewdotn, лучший способ — увидеть подробную ошибку (SHOW ENGINE INNODB STATUS;
) вместо просто кода ошибки.
Одной из причин может быть то, что индекс с таким именем уже существует, может быть в другой таблице. На практике я рекомендую ставить префикс имени таблицы перед именем индекса, чтобы избежать таких коллизий. например вместо idx_userId
использование idx_userActionMapping_userId
,