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

Тема

Проблема с внешним ключом MariaDB InnoDB

Хочу начать с того, что я новичок в InnoDB и вчера весь день читал сообщения. Я пробовал несколько вещей на этом пути, чтобы добраться туда, где я сейчас, так что я облился водой или есть выход из этого темного леса.

У меня есть таблица, которая является центральной для ряда таблиц в моей модели данных. Итак, что-то в этом роде:

create table users (id int not null auto_increment
, username varchar(255) NOT NULL
, password varchar(255) NOT NULL
, active int NOT NULL
, PRIMARY KEY (id))
ENGINE=InnoDB COLLATE=utf8_unicode_ci;

В эти выходные решил быстро убрать некоторые пункты DELETE / UPDATE в моих FK... Знаменитые последние слова...

Пример связанной таблицы здесь

create table athing (id int not null auto_increment
, name varchar(255) not null
, status varchar(255) not null
, created_by_user_id int 
, PRIMARY KEY (id)
, CONSTRAINT athing_fk1 FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB COLLATE=utf8_unicode_ci;

Проблема

Изменен FK в таблице «ATHING», чтобы включить ON DELETE SET NULL. Сохранил эту модификацию вроде все ок. Для этого я использовал HeidiSQL.

Короче говоря, я просматривал свой список столов и опустился, и вот, моя таблица USERS ИСЧЕЗЛА! Благодаря большому чтению и усилиям я смог навести порядок, но, чтобы убедиться, что все действительно хорошо, я удалил все FK, указывающие на таблицу USERS, и удалил таблицу.

Теперь, когда я пытаюсь воссоздать таблицу USERS, я получаю эту ошибку:

ERROR 1005 (HY000): Can't create table `sprintdb`.`system_users` (errno: 150 "Foreign key constraint is incorrectly formed")

Что я заметил после моей первой попытки сделать это, так это то, что, хотя я думал, что сбросил все FK, там все еще оставались остатки ключей, в частности индексы, которые поддерживали эти ключи в некоторых таблицах. При запросе таблиц INNODB_SYS_TABLES и INNODB_SYS_INDEXES те индексы, которые я считал удаленными, все еще существуют в этих системных таблицах.

Есть ли способ выйти за рамки этого? Я чувствую, что где-то существует какая-то часть информации, будь то в файловой системе или в самой базе данных, которую необходимо обновить или удалить, чтобы я мог двигаться вперед... мысли?


mysql database mariadb foreign-key-relationship
person John    schedule 08.06.2014    source источник
comment
Я в замешательстве. В коротком абзаце «Длинный рассказ» вы сначала говорите, что таблица USER исчезла, а затем удалили внешние ключи и таблицу USERS? Поскольку на таблицу USERS ссылались другие таблицы, я бы подумал, что ее удаление приведет к ошибкам. У вас есть резервная копия БД?   -  person ron tornambe    schedule 08.06.2014
comment
Да, извините, во время этого процесса возник ряд проблем. Я буквально потерял таблицу, смог восстановить ее структуру без данных, и для обеспечения согласованности и проверки того, что все в порядке, я решил удалить все внешние ключи из всех ссылающихся таблиц и удалить таблицу USERS. Когда я пытаюсь создать таблицу USERS, результатом является ошибка, указанная выше. Сбой резервного копирования из-за исчезновения таблицы USERS, что вызвало представление с именем типа active_blah, которое оказалось первым объектом в sqldump и не удалось из-за ошибок...   -  person John    schedule 09.06.2014
comment
Печаль во благо. Мерфи нанес вам визит. Можете ли вы создать новую базу данных и применить к ней резервную копию?   -  person ron tornambe    schedule 09.06.2014
comment
Положительным моментом является то, что это основная среда разработки без большого количества данных... к сожалению, разработчики борются за среду... так что я пытаюсь найти кратчайший путь к исправлению это. Я прочитал что-то, указывающее, что если я перестрою каждую из таблиц, у которых есть ссылки FK на эту таблицу, это должно исправить проблему. Является ли это фактом или вымыслом, и или я должен просто эффективно воссоздать среду из моего инструмента моделирования и перемещать данные, насколько это возможно?   -  person John    schedule 09.06.2014
comment
Если у вас есть сохраненная модель данных или ее относительно легко воссоздать, то, конечно, я бы стиснул зубы и начал с нуля, вместо того, чтобы тратить время на бесполезные трюки. Вы можете даже найти улучшения в модели данных. Часто, когда я переписываю код, он становится лучше. Напомните разработчикам, что никогда не бывает времени, чтобы сделать это правильно, но всегда есть время, чтобы сделать это заново.   -  person ron tornambe    schedule 09.06.2014

Ответы (1)


arrow_upward
19
arrow_downward

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

  • Столбцы int имеют разные размеры
  • Столбцы int имеют разные флаги (без AUTO_INCREMENT)

В качестве примера я создал таблицу с помощью инструмента, который каким-то образом создал столбец как INT(10) вместо ожидаемого INT(11). Несмотря на то, что я просто выбрал INT при создании обоих, это было перепутано - так и не выяснил, почему.

Короче говоря, обычно лучше явно указывать размер INT при создании таблицы.

В вашем случае должно работать следующее:

create table users (id int(11) not null auto_increment
, username varchar(255) NOT NULL
, password varchar(255) NOT NULL
, active int NOT NULL
, PRIMARY KEY (id))
ENGINE=InnoDB COLLATE=utf8_unicode_ci;

create table athing (id int(11) not null auto_increment
, name varchar(255) not null
, status varchar(255) not null
, created_by_user_id int(11) not null
, PRIMARY KEY (id)
, CONSTRAINT athing_fk1 FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB COLLATE=utf8_unicode_ci;
person h0tw1r3    schedule 10.08.2014
comment
Также стоит проверить соответствие движков хранения в таблицах. Например, попытка установить FK между InnoDB и MyISAM потерпит неудачу. - person darronz; 17.11.2015
comment
Я тоже испытал то же самое. Я не знаю как, но мой внешний ключ был установлен на varchar вместо INT(11) XD - person Lynnell Neri; 21.02.2017
comment
Флаг unsigned тоже вызывает проблемы - person Choma; 15.02.2018
comment
Другое дело, что вам нужно проверить параметры сортировки, если вы используете ключи varchar или char. - person Ahfa; 05.05.2021