MySQL не может создать ограничение внешнего ключа

У меня возникли проблемы с созданием внешнего ключа для существующей таблицы в базе данных mysql.

У меня есть таблица exp:

+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| EID         | varchar(45)      | NO   | PRI | NULL    |       |
| Comment     | text             | YES  |     | NULL    |       |
| Initials    | varchar(255)     | NO   |     | NULL    |       |
| ExpDate     | date             | NO   |     | NULL    |       |
| InsertDate  | date             | NO   |     | NULL    |       |
| inserted_by | int(11) unsigned | YES  | MUL | NULL    |       |
+-------------+------------------+------+-----+---------+-------+

и я не хочу создавать новую таблицу с именем sample_df, ссылающуюся на это, используя следующее:

CREATE TABLE sample_df (
df_id mediumint(5) unsigned AUTO_INCREMENT primary key,
sample_type mediumint(5) unsigned NOT NULL,
df_10 BOOLEAN NOT NULL,
df_100 BOOLEAN NOT NULL,
df_1000 BOOLEAN NOT NULL,
df_above_1000 BOOLEAN NOT NULL,
target INT(11) unsigned NOT NULL,
assay MEDIUMINT(5) unsigned zerofill NOT NULL,
insert_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
inserted_by INT(11) unsigned NOT NULL,
initials varchar(255),
experiment VARCHAR(45),
CONSTRAINT FOREIGN KEY (inserted_by) REFERENCES user (iduser),
CONSTRAINT FOREIGN KEY (target) REFERENCES protein (PID),
CONSTRAINT FOREIGN KEY (sample_type) REFERENCES sample_type (ID),
CONSTRAINT FOREIGN KEY (assay) REFERENCES assays (AID),
CONSTRAINT FOREIGN KEY (experiment) REFERENCES exp (EID)
);

Но я получаю ошибку:

ERROR 1215 (HY000): Cannot add foreign key constraint

Чтобы получить больше информации, я сделал:

SHOW ENGINE INNODB STATUS\G

Из чего я получил:

FOREIGN KEY (experiment) REFERENCES exp (EID)
):
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.

Мне кажется, что типы столбцов совпадают, так как они оба varchar (45). (Я также пытался установить для столбца experiment значение не ноль, но это не устранило проблему). это Cannot find an index in the referenced table where the referenced columns appear as the first columns. Но я не совсем уверен, что это значит, или как это проверить/исправить. У кого-нибудь есть предложения? А что подразумевается под first columns?

Ответ 1

Просто перебрасывая это в сочетание возможных причин, я столкнулся с этим, когда столбец таблицы ссылок имел тот же "тип", но не имел того же самого подписания.

В моем случае ссылочный столбец таблицы был TINYINT UNSIGNED, а мой столбец таблицы ссылок был TINYINT SIGNED. Выравнивание обоих столбцов позволило решить проблему.

Ответ 2

Согласно http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html

MySQL требует индексов для внешних ключей и ссылочных ключей, чтобы проверки внешнего ключа могут быть быстрыми и не требуют сканирования таблицы. в таблицы ссылок, должен быть индекс, где внешний ключ столбцы перечислены в качестве первых столбцов в том же порядке.

InnoDB позволяет внешнему ключу ссылаться на любой индексный столбец или группу колонны. Однако в ссылочной таблице должен быть индекс где ссылочные столбцы перечислены в качестве первых столбцов в тот же порядок.

Итак, если индекс в ссылочной таблице существует и состоит из нескольких столбцов, а нужный столбец не первый, ошибка должна произойти.

Причиной нашей ошибки было нарушение следующего правила:

Соответствующие столбцы внешнего ключа и ссылочного ключа должны имеют аналогичные типы данных. Размер и знак целочисленных типов должны быть тоже самое. Длина типов строк не обязательно должна быть одинаковой. Для небинные (символьные) строковые столбцы, набор символов и сортировка должны быть одинаковыми.

Ответ 3

Эта ошибка также может возникнуть, если таблица ссылок и текущая таблица не имеют одинакового набора символов.

Ответ 4

Как упоминалось @Anton, это может быть из-за разных типов данных. В моем случае у меня был первичный ключ BIGINT (20) и попытался установить передний ключ с INT (10)

Ответ 5

В моем случае оказалось, что ссылочный столбец не был объявлен как первичный или уникальный.

fooobar.com/questions/68465/...

Ответ 6

Решение, предложенное @austen-hoogen, решило мою проблему. Но из-за низких оценок репутации я не могу голосовать и комментировать его решение. В моем случае Первичный ключ был целым, автоматическим приращением и без знака. Но внешний ключ (столбец ссылочной таблицы) был подписан типом. Я изменил его на unsigned и смог создать связь успешно.

Ответ 7

Точный порядок первичного ключа также должен совпадать без дополнительных столбцов между ними.

У меня была установка первичного ключа, в которой порядок столбцов фактически совпадает, но проблема заключалась в том, что первичный ключ содержал дополнительный столбец, который не является частью внешнего ключа ссылочной таблицы

например, таблица 2, столбец (a, b, c) → таблица 1, столбец (a, b, d, c) - ЭТО ОШИБКИ

Мне пришлось изменить порядок столбцов первичного ключа, чтобы они не только упорядочивались одинаково, но и не имели дополнительных столбцов посередине:

например, таблица 2, столбец (a, b, c) → таблица 1, столбец (a, b, c, d) - ЭТО УСПЕШНО

Ответ 8

Ссылка на один и тот же столбец более одного раза в одном и том же ограничении также вызывает эту ошибку Cannot find an index in the referenced table, но ее трудно обнаружить на больших таблицах. Разделите ограничения, и он будет работать, как ожидалось.

Ответ 9

У меня тоже была эта ошибка. Ни один из ответов не относился ко мне. В моем случае мой графический интерфейс автоматически создает таблицу с первичным уникальным идентификатором как "неназначенный". Это не удается, когда я пытаюсь создать внешний ключ, и выдает мне точно такую же ошибку. Мой первичный ключ должен быть назначен.

Если вы пишете сам SQL примерно так id int unique auto_increment, то у вас нет этой проблемы, но по какой-то причине мой GUI делает это вместо этого id int unassigned unique auto_increment.

Надеюсь, что это поможет кому-то еще в будущем.

Ответ 10

У меня возникла проблема сопоставления между ссылочной таблицей и таблицей, которую нужно создать, поэтому мне пришлось явно установить тип сопоставления ключа, на который я ссылался.

Сначала я запустил запрос к ссылочной таблице, чтобы получить ее тип сортировки

show table STATUS like '<table_name_here>';

Затем я скопировал тип сортировки и явно указал тип сортировки employee_id в запросе на создание. В моем случае это было utf8_general_ci

CREATE TABLE dbo.sample_db
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_id INT SIGNED NOT NULL,
  employee_id varchar(45) COLLATE utf8_general_ci NOT NULL,
  event_date_time DATETIME,
  CONSTRAINT sample_db_event_event_id_fk FOREIGN KEY (event_id) REFERENCES event (event_id),
  CONSTRAINT sample_db_employee_employee_id_fk FOREIGN KEY (employee_id) REFERENCES employee (employee_id)
);

Ответ 11

В некоторых случаях мне приходилось делать поле, на которое ссылаются, уникальным поверх определения его в качестве первичного ключа.

Но я обнаружил, что определение его как уникального не создает проблемы в каждом случае. Я не смог выяснить сценарии, хотя. Вероятно, что-то делать с обнуляемым определением.