проблема при добавлении внешнего ключа с помощью alter table к существующей базе данных mysql - не могу добавить его! помогите!

<p>у меня есть производственная база данных, в которой я переименовал несколько столбцов, которые являются внешними ключами. очевидно, mysql делает это реальной болью в моем опыте.</p><p>мое решение состояло в том, чтобы удалить все индексы и внешние ключи, переименовать столбцы идентификаторов, а затем повторно добавить индексы и внешние ключи.</p><p>это прекрасно работает на mysql 5.1 на окнах для базы данных разработки.</p><p>я пошел, чтобы запустить мой скрипт миграции на моем сервере debian, который также использует mysql 5.1, и он выдает следующую ошибку:</p><pre><code>mysql> alter table `company\_to\_module` -> add constraint `fk82977604fe40a062` foreign key (`company\_id`) references `company` (`company\_id`) on delete restrict on update restrict; error 1005 (hy000): can't create table 'jobprep\_production.#sql-44a5\_76' (errno: 150) </code></pre><p>в этой таблице нет значений, которые бы конфликтовали с внешним ключом, который я пытаюсь добавить. база данных не изменилась. внешний ключ did существовал раньше ... поэтому данные в порядке. не будем упоминать, что я взял ту же самую базу данных, которая у меня есть на сервере, и она прекрасно переносится в windows. но эти же миграции с использованием внешних ключей не берут debian.</p><p>столбцы используют один и тот же тип - bigint (20)</p><p>имена действительно существуют в соответствующих таблицах.</p><p>столы innodb. у них уже есть внешние ключи в других столбцах, как есть. это не новая база данных.</p><p>я не могу удалить таблицы, потому что это производственная база данных.</p><p>таблицы "как есть" в моей базе данных:</p><pre><code> create table `company\_to\_module` ( `company\_id` bigint(20) not null, `module\_id` bigint(20) not null, key `fk8297760442c8f876` (`module\_id`), key `fk82977604fe40a062` (`company\_id`) using btree, constraint `fk8297760442c8f876` foreign key (`module\_id`) references `module` (`module\_id`) ) engine=innodb default charset=utf8 </code></pre><p>а также</p><pre><code>create table: create table `company` ( `company\_id` bigint(20) not null auto\_increment, `name` varchar(255) default null, `address` varchar(255) default null, `postal\_code` varchar(255) default null, `province\_id` bigint(20) default null, `phone\_number` varchar(255) default null, `is\_enabled` bit(1) default null, `director\_id` bigint(20) default null, `homepage\_viewable` bit(1) not null default b'1', `courses\_created` int(10) not null default '0', `header\_background` varchar(25) default '#172636', `display\_name` varchar(25) default '#ffffff', `tab\_background` varchar(25) default '#284767', `tab\_text` varchar(25) default '#ffffff', `hover\_tab\_background` varchar(25) default '#284767', `hover\_tab\_text` varchar(25) default '#f2e0bd', `selected\_tab\_background` varchar(25) default '#f5f5f5', `selected\_tab\_text` varchar(25) default '#172636', `hover\_table\_row\_background` varchar(25) default '#c0d2e4', `link` varchar(25) default '#4e6c92', primary key (`company\_id`), key `fk61ae555a71df3e03` (`province\_id`), key `fk61ae555aac50c977` (`director\_id`), constraint `company\_ibfk\_1` foreign key (`director\_id`) references `user\_account` (`user\_account\_id`), constraint `fk61ae555a71df3e03` foreign key (`province\_id`) references `province` (`province\_id`) ) engine=innodb auto\_increment=24 default charset=utf8 </code></pre><p>вот статус innodb:</p><pre><code>------------------------ latest foreign key error ------------------------ 110415 3:14:34 error in foreign key constraint of table jobprep\_production/#sql-44a5\_1bc: foreign key (`company\_id`) references `company` (`company\_id`) on delete restrict on update restrict: cannot resolve column name close to: ) on delete restrict on update restrict </code></pre><p>если я пытаюсь удалить индекс из "company\_to\_module", я получаю эту ошибку:</p><pre><code>#1025 - error on rename of './jobprep\_production/#sql-44a5\_23a' to './jobprep\_production/company\_to\_module' (errno: 150) </code></pre><p>вот мои переменные innodb:</p><pre><code>+---------------------------------+------------------------+ | variable\_name | value | +---------------------------------+------------------------+ | innodb\_adaptive\_hash\_index | on | | innodb\_additional\_mem\_pool\_size | 1048576 | | innodb\_autoextend\_increment | 8 | | innodb\_autoinc\_lock\_mode | 1 | | innodb\_buffer\_pool\_size | 8388608 | | innodb\_checksums | on | | innodb\_commit\_concurrency | 0 | | innodb\_concurrency\_tickets | 500 | | innodb\_data\_file\_path | ibdata1:10m:autoextend | | innodb\_data\_home\_dir | | | innodb\_doublewrite | on | | innodb\_fast\_shutdown | 1 | | innodb\_file\_io\_threads | 4 | | innodb\_file\_per\_table | off | | innodb\_flush\_log\_at\_trx\_commit | 1 | | innodb\_flush\_method | | | innodb\_force\_recovery | 0 | | innodb\_lock\_wait\_timeout | 50 | | innodb\_locks\_unsafe\_for\_binlog | off | | innodb\_log\_buffer\_size | 1048576 | | innodb\_log\_file\_size | 5242880 | | innodb\_log\_files\_in\_group | 2 | | innodb\_log\_group\_home\_dir | ./ | | innodb\_max\_dirty\_pages\_pct | 90 | | innodb\_max\_purge\_lag | 0 | | innodb\_mirrored\_log\_groups | 1 | | innodb\_open\_files | 300 | | innodb\_rollback\_on\_timeout | off | | innodb\_stats\_on\_metadata | on | | innodb\_support\_xa | on | | innodb\_sync\_spin\_loops | 20 | | innodb\_table\_locks | on | | innodb\_thread\_concurrency | 8 | | innodb\_thread\_sleep\_delay | 10000 | +---------------------------------+------------------------+ </code></pre><p>я также хочу добавить, что во время игры с добавлением внешних ключей mysql повредил мою базу данных и уничтожил ее. мне пришлось перезагрузить из резервной копии, чтобы повторить попытку.</p><p>помогите? : /</p>

<p>я просто применил рефакторинг с помощью windows, а затем снова импортировал базу данных в debian - это работает.</p><p>я думаю, можно с уверенностью сказать, что что-то напутало на сервере debian или в версии mysql для linux - возможно, ошибка в сборке 5.1?</p><p>во всяком случае, я также обновил оперативную память на сервере с 1 гб до 2 гб, и эти проблемы исчезли.</p><p>я думаю, что mysql, возможно, просто не хватило оперативной памяти для завершения операции. если бы это было так (и, похоже, так), я думаю, что mysql должен был просто сказать об этом, а не выплевывать эти ошибки - заставляя меня и всех присутствующих думать, что это был синтаксис или проблема, связанная со схемой.</p><p>в любом случае, спасибо за тех, кто пытался помочь. по крайней мере, это помогло мне изолировать все то, чего не могло быть.</p>

<p>являются ли обе таблицы innodb типа?</p><p>есть ли у таблицы компании индекс company\_id?</p><p>я предполагаю, что ваша таблица - myisam (по умолчанию, если вы не изменили конфигурацию), и вы не можете создавать ограничения внешнего ключа в myisam. смотрите описание create table для двух таблиц.</p><p>если обе таблицы пусты, удалите их и создайте заново, выбрав innodb в качестве движка. вы также можете добавить ограничения foreign key в сценарии создания таблиц.</p><hr><p>из mysql <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html">справочное руководство</a> :</p><blockquote> <p>на определения внешних ключей распространяются следующие условия:</p> <ul> <li> <p><strong> обе таблицы должны быть таблицами innodb </strong>, и они не должны быть временными таблицами.</p> </li> <li> <p><strong> соответствующие столбцы </strong> во внешнем ключе и ссылочном ключе <strong> должны иметь схожие внутренние типы данных </strong> внутри innodb, чтобы их можно было сравнивать без преобразования типов. <strong> размер и знак целочисленных типов должны быть одинаковыми </strong>. длина типов строк не обязательно должна быть одинаковой. <strong> для недвоичных (символьных) строковых столбцов набор символов и параметры сортировки должны быть одинаковыми </strong>.</p> </li> <li> <p><strong> innodb требует индексов для внешних ключей </strong> и ссылочных ключей, чтобы проверка внешних ключей могла быть быстрой и не требовала сканирования таблицы. в ссылочной таблице должен быть индекс, в котором столбцы внешнего ключа перечислены как первые столбцы в том же порядке. <strong> такой индекс создается в ссылочной таблице автоматически, если он не существует </strong>. (это отличается от некоторых более старых версий, в которых индексы должны были создаваться явно, или создание ограничений внешнего ключа не удавалось.) index\_name, если дано, используется, как описано ранее.</p> </li> <li> <p>innodb позволяет внешнему ключу ссылаться на любой столбец индекса или группу столбцов. однако в ссылочной таблице должен быть индекс, в котором указанные столбцы перечислены как первые столбцы в том же порядке.</p> </li> <li> <p>префиксы индекса для столбцов внешнего ключа не поддерживаются. одним из следствий этого является то, что <strong> столбцы blob и text не могут быть включены во внешний ключ </strong>, поскольку индексы в этих столбцах должны всегда включать длину префикса.</p> </li> <li> <p>если задано предложение символа constraint, значение символа должно быть уникальным в базе данных. если предложение не указано, innodb создает имя автоматически.</p> </li> </ul> </blockquote><hr><p>@egervari: что произойдет, если вы запустите это:</p><pre><code>create table `test` ( `company\_id` bigint(20) not null, `module\_id` bigint(20) not null, key (`module\_id`), key (`company\_id`), constraint `test\_fk\_module` foreign key (`module\_id`) references `module` (`module\_id`), constraint `test\_fk\_company` foreign key (`company\_id`) references `company` (`company\_id`) on delete restrict on update restrict ) engine=innodb default charset=utf8 ; </code></pre><hr><p>и если вы запустите:</p><pre><code>alter table `company\_to\_module` add constraint `company\_to\_module\_fk\_company` foreign key (`company\_id`) references `company` (`company\_id`) on delete restrict on update restrict; </code></pre>

<p>убедитесь, что company\_to\_module.company\_id и company.company\_id являются точным одним и тем же типом данных. я имел это, когда первичный ключ был установлен как не подписано int, но поле внешнего ключа было просто int. добавление unsigned в тип данных устранило проблему.</p>

<p>@egervari вы писали: мое решение состояло в том, чтобы удалить все индексы и внешние ключи, переименовать столбцы id, а затем повторно добавить индексы и внешние ключи.</p><p>согласен с тобой. но, возможно, что-то пошло не так. я воспроизвел ошибку и (в моем случае) исправил ее.</p><p>я бы посоветовал вам запустить команду optimize table для таблицы, столбец которой был переименован. документация говорит - <em>для таблиц innodb optimize table сопоставляется с alter table, который перестраивает таблицу для обновления статистики индекса и освобождения неиспользуемого пространства в кластерном индексе.</em></p><hr><p>еще одно решение:</p><p>удалите уникальный ключ в ссылочной таблице (ключ, который используется внешним ключом, в вашем случае это первичный ключ). затем добавьте новый внешний ключ и воссоздайте удаленный уникальный ключ.</p><hr><p>еще одно решение:</p><p>попробуйте добавить и удалить новый столбец в ссылочной таблице, затем попытайтесь создать свой внешний ключ.</p><pre><code>alter table company add column column1 varchar(255) default null; alter table company drop column column1; </code></pre>

<p>поскольку попытка создать копию <code>company\_to\_module</code> вручную приводит к той же ошибке, вам следует тщательно проверить ограничение fk, уже присутствующее в <code>company\_to\_module</code>. это все еще допустимо, или вы изменили таблицу <code>module</code>?</p><p>из <a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-error-codes.html">mysql-docs</a> :</p><blockquote> <p>1005 (er\_cant\_create\_table) невозможно создать таблицу. если сообщение об ошибке ссылается на ошибку 150, создание таблицы не удалось, поскольку ограничение внешнего ключа было сформировано неправильно.</p> </blockquote>

<p>поскольку это не имеет ничего общего с синтаксисом, я думаю, вам не хватает места для создания таблиц innodb.</p><p>правка: можете ли вы вставить вашу конфигурацию innodb:</p><pre><code>show variables like "inno%"; </code></pre>