1005 phpmyadmin ошибка

I searched for a solution to this problem on the Internet and checked the Stack Overflow questions, but none of the solutions worked for my case.

I want to create a foreign key from table sira_no to metal_kod.

ALTER TABLE sira_no
    ADD CONSTRAINT METAL_KODU FOREIGN KEY(METAL_KODU)
    REFERENCES metal_kod(METAL_KODU)
    ON DELETE SET NULL
    ON UPDATE SET NULL ;

This script returns:

Error Code: 1005. Can't create table 'ebs.#sql-f48_1a3' (errno: 150)

I tried adding an index to the referenced table:

CREATE INDEX METAL_KODU_INDEX ON metal_kod (METAL_KODU);

I checked METAL_KODU on both tables (charset and collation), but I couldn’t find a solution to this problem. How can I fix this problem?

Here is the metal_kod table:

METAL_KODU    varchar(4)    NO    PRI
DURUM    bit(1)    NO
METAL_ISMI    varchar(30)    NO
AYAR_YOGUNLUK    smallint(6)    YES        100

Very often it happens when the foreign key and the reference key don’t have the same type or same length.

Sometimes it is due to the master table is dropped (maybe by disabling foreign_key_checks), but the foreign key CONSTRAINT still exists in other tables. In my case I had dropped the table and tried to recreate it, but it was throwing the same error for me.

So try dropping all the foreign key CONSTRAINTs from all the tables if there are any and then update or create the table.

I had a similar error. The problem had to do with the child and parent table not having the same charset and collation. This can be fixed by appending ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `country` (`id` INT(11) NOT NULL AUTO_INCREMENT,...) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

… on the SQL statement means that there is some missing code.

answered Jul 14, 2016 at 8:34

Error Code: 1005

I had a similar issue, so here are few things that I did try (not in any order, except for the solution )

1. Changed the foreign key names (it didn’t work)
2. Reduced the foreign key length
3. Verified the datatypes (darn nothing wrong)
4. Check indexes
5. Check the collations (everything fine, darn again)
6. Truncated the table, of no good use
7. Dropped the table and re-created
8. Tried to see if any circular reference is being created — all fine

9. Finally, I saw that I had two editors open. One that in PhpStorm (JetBrains) and the other MySQL workbench. It seems that the PhpStorm / MySQL Workbench creates some kind of edit lock.

   I closed PhpStorm just to check if locking was the case (it could have been the other way around). This solved my problem.

I had the very same error message. Finally I figured out I misspelled the name of the table in the command:

ALTER TABLE `users` ADD FOREIGN KEY (country_id) REFERENCES country (id);

versus

ALTER TABLE `users` ADD FOREIGN KEY (country_id) REFERENCES countries (id);

I wonder why on earth MySQL cannot tell such a table does not exist…

MyISAM has been just mentioned. Simply try adding ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; at the end of a statement, assuming that your other tables were created with MyISAM.

CREATE TABLE IF NOT EXISTS `tablename` (
  `key` bigint(20) NOT NULL AUTO_INCREMENT,
  FOREIGN KEY `key` (`key`) REFERENCES `othertable`(`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

answered Jan 20, 2016 at 23:29

answered Mar 28, 2016 at 2:01

It happened in my case, because the name of the table being referenced in the constraint declaration wasn’t correct (I forgot the upper case in the table name):

ALTER TABLE `Window` ADD CONSTRAINT `Windows_ibfk_1` FOREIGN KEY (`WallId`) REFERENCES `Wall` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

answered Sep 21, 2019 at 2:35

this is my code…i have no idea what i am doing wrong because i am new to this. i am getting an 1005 error saying that i cannot create the table bookauthor.

CREATE TABLE Book_Author(
    BID INTEGER(7) NOT NULL,
    B_TITLE VARCHAR(25) NOT NULL,
    AID INTEGER(7) NOT NULL,
    A_NAME VARCHAR(25) NOT NULL,
        CONSTRAINT PRIMARY KEY(BID),
        CONSTRAINT BID_FK
          FOREIGN KEY(BID)
          REFERENCES Book_Info(BID)
          ON DELETE CASCADE
);

You should try it following way

CREATE TABLE IF NOT EXISTS `Book_Author` (
  `BID` int(7) NOT NULL,
  `B_TITLE` varchar(25) NOT NULL,
  `AID` int(7) NOT NULL,
  `A_NAME` varchar(25) NOT NULL,
  PRIMARY KEY (`BID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then use CASCADE

ALTER TABLE `Book_Author`
      ADD CONSTRAINT `BookAuthor` FOREIGN KEY (`BID`) 
REFERENCES Book_Info(BID) ON DELETE CASCADE;

answered Feb 21, 2015 at 3:29

There are two possibilities in your case

You might not have used the same data type as the parent table column
(ex: parent table BID is varchar and in the child table BID is Integer(7))

or Referenced column is not a primary key on the parent table

Try this

create table Book_Info(BID INTEGER(7) NOT NULL,
                      CONSTRAINT PRIMARY KEY(BID));

CREATE TABLE Book_Author(
    BID INTEGER(7) NOT NULL,
    B_TITLE VARCHAR(25) NOT NULL,
    AID INTEGER(7) NOT NULL,
    A_NAME VARCHAR(25) NOT NULL,
        CONSTRAINT PRIMARY KEY(BID),
        CONSTRAINT BID_FK
          FOREIGN KEY(BID)
          REFERENCES Book_Info(BID)
          ON DELETE CASCADE
);

• Sample Demo

answered Feb 21, 2015 at 3:55

1005 (ER_CANT_CREATE_TABLE)

Cannot create table. If the error message refers to error 150, table
creation failed because a foreign key constraint was not correctly
formed. If the error message refers to error –1, table creation
probably failed because the table includes a column name that matched
the name of an internal InnoDB table.

My guess would be that this is because you haven’t created a PRIMARY KEY for you Book_Info table. You are trying to create a foreign key constraint and that requires that both table has valid index. You can add a primary key on your Book_Info table like this:

ALTER TABLE Book_Info ADD PRIMARY KEY BID_PK(BID);

Another thing that @NoDisplayName pointed out is that you should check so the two columns is of the same type and length.

You could check the status of your engine and try to find out more about this error:

SHOW ENGINE INNODB STATUSG

Then you might see a more descriptive error message like this:

———————— LATEST FOREIGN KEY ERROR ————————
2015-02-21 05:13:34 1116b0000 Error in foreign key constraint of table test/book_author:
FOREIGN KEY(BID) REFERENCES Book_Info(BID) ON DELETE CASCADE ):
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.
Note that the internal storage type of ENUM and SET
changed in tables created with >= InnoDB-4.1.12, and such columns in
old tables cannot be referenced by such columns in new tables.
See
http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

answered May 15, 2017 at 2:51

#1005 error Solved :-
Parent Table//
CREATE TABLE gender(

 id int not null AUTO_INCREMENT PRIMARY KEY,
 name varchar(50)

);

Child Table//
CREATE TABLE students (

id int not null AUTO_INCREMENT PRIMARY KEY,
name varchar(50),
fatherName varchar(50),
gender_id int not null,
CONSTRAINT fk_gender_id FOREIGN KEY (gender_id) REFERENCES gender(id) on DELETE SET null

);
In Child table you write not null means that this column does not allow null values and in foreign key you are saying that after deleting record from parent table put NULL in column (gender_id) but that column will not allow NULL values that’s why error #1005 occurs

answered Sep 15, 2017 at 12:26

create table t1 (a int, b int, index(a)) engine=innodb;

create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

SHOW ENGINE INNODB STATUS;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-16 06:37:39 0x14c1c Error in foreign key constraint of table test/t2:
foreign key (a) references t1(b)) engine=innodb:
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constr
aints.html for correct foreign key definition.
————

— при использовании оператора ALTER ошибка и секция
— LATEST FOREIGN KEY ERROR будут содержать внутреннее имя таблицы test.#sql-a64_1

create table t2 (a int) engine=innodb;
alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1005 (HY000): Cannot create table ‘test.#sql-a64_1’ (errno: 150)

— в новых версиях парсер MySQL определяет некорректность
— конструкции и возвращает другую ошибку (без errno 150)

alter table t2 add foreign key (a) references t1(a), add foreign key (a) references t1(b);
ERROR 1215 (HY000): Cannot add foreign key constraint

— аналогично и для оператора CREATE

drop table t2;
create table t2 (a int, foreign key (a) references t1(a), foreign key (a) references t1(b)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

create table t1 (a int, b int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

alter table t1 drop a;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_5′ to ‘.testt1′ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161220  7:14:25 Error in foreign key constraint of table test/t2:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
  CONSTRAINT «t2_ibfk_1» FOREIGN KEY («a») REFERENCES «t1» («a»)
The index in the foreign key in table is «a»
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
InnoDB: Renaming table `test`.`#sql-d6c_5` to `test`.`t1` failed!
———

create table t1(id int not null primary key, b int, key(b)) engine=innodb;
Query OK, 0 rows affected (0.22 sec)

alter table t1 add foreign key(id,b) references t1(id);
ERROR 1005 (HY000): Can‘t create table ‘test.#sql-d88_b’ (errno: 150)

show warnings;
+——-+——+—————————————————+
| Level | Code | Message                                           |
+——-+——+—————————————————+
| Error | 1005 | Can‘t create table ‘test.#sql-d88_b’ (errno: 150) |
+——-+——+—————————————————+

— понять, что причина в синтаксической ошибке
— можно только из:

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
160605 22:28:23 Error in foreign key constraint of table test/#sql-d88_b:
foreign key(id,b) references t1(id):
Syntax error close to:

— в новых версиях парсер распознает синтаксическую ошибку
— и сообщает об этом:
ERROR 1239 (42000): Incorrect foreign key definition for ‘foreign key without name’: Key reference and table reference don‘t match

create table t1 (a text , index(a(50))) engine=innodb;

create table t2 (a text, foreign key (a) references t1(a)) engine=innodb;
ERROR 1170 (42000): BLOB/TEXT column ‘a’ used in key specification without a key length

— MySQL автоматически пытается создать индекс на колонке `a`, и
— сообщает, что нельзя создать индекс по всей длине поля типа text.
— Хорошо, укажем префикс и получим errno 150:

create table t2 (a text, foreign key (a(50)) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

— понять, что произошла ошибка синтаксиса можно:
— или через show engine innodb status;
— или внимательно сравнить разрешенный синтаксис в документации
— с написанной командой.

— например, если у одной колонки мы определим
— атрибут unsigned, а у другой нет, то:
create table t1 (a int unsigned, index(a)) engine=innodb;

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

— в старых версиях будет ошибка вида
ERROR 1005 (HY000): Cannott create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 03:00:47 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a)) engine=innodb:
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constr
aints.html for correct foreign key definition.
————

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

MariaDB [test]> alter table t1 modify a int unsigned;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt1′ (errno: 150)

create table t1 (a int not null, index(a)) engine=innodb;

create table t2 (a int not null, foreign key (a) references t1(a) on delete set null) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

— в старых версиях будет:
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
2016-11-26 06:24:42 0x10894 Error in foreign key constraint of table world/t2:
foreign key (a) references t1(a) on delete set null) engine=innodb:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.
————

create table t1 (a int not null, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a) on delete set null) engine=innodb;

alter table t2 modify a int not null;
ERROR 1025 (HY000): Error on rename of ‘.test#sql-d6c_6′ to ‘.testt2′ (errno: 150)

create table t1 (a int, index(a)) partition by range (a)  
(partition p0 values less than (10),
partition p1 values less than (20),
partition p2 values less than maxvalue);

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1005 (HY000): Cannot create table ‘test.t2’ (errno: 150)

show engine innodb status;
————————
LATEST FOREIGN KEY ERROR
————————
161223 19:38:14 Error in foreign key constraint of table test/t2:
foreign key (a) references t1(a)) engine=innodb:
Cannot resolve table name close to:
(a)) engine=innodb
———
— сообщение указывает на то, что родительская таблица
— не найдена в словаре данных innodb (bug: 84331)

— в новых версиях ошибка будет иметь вид:

create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

create table t1 (a int, index(a)) engine=innodb;
create table t2 (a int, foreign key (a) references t1(a)) engine=innodb;

alter table t1 PARTITION BY HASH(a) PARTITIONS 8;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

show engine innodb status;
— не содержит секцию LATEST FOREIGN KEY ERROR

Важно, чтобы у вас были права доступа к базе данных. Вы должны попросить своего системного администратора предоставить права на ваш доступ, и если вы являетесь администратором, убедитесь, что вы выполняете это действие как root

Ещё вопросы

• 1Возможны ли два пакета в одном APK?
• 0Как удалить тег IMG из HTML
• 1Как запретить другому потоку изменять локальную ссылочную переменную в методе во время его работы?
• 0Как отличить s от ş в подобном поиске
• 1c # — работа с COM + объектом в WCF
• 0как искать часть строки, а не все
• 0Существует ли кроссплатформенная библиотека для событий файловой системы?
• 0Директива общего доступа между приложениями в AngularJS
• 1Linq обновляет другую таблицу после процесса соединения
• 0Chrome показывает Access Control Allow Origin ошибка
• 0Javascript ссылка на изображения LIGHTBOX
• 0Как работать с аргументами в Xcode
• 1Как получить пользователей из списка LiveData для отображения в Spinner с использованием базы данных комнат
• 0Как вставить новую строку в таблицу через angularjs?
• 0Как получить текст из диалогового окна textarea jquery UI
• 0как предотвратить двойной щелчок мышью (за раз)
• 1Форматирование файла в список с ценой, названием продукта и количеством
• 1Насколько эффективна эта хеш-функция?
• 1метрика f1_score в лайтбм
• 1python / pandas извлекают определенный столбец из мультииндекса
• 0Ошибка: недопустимое значение для атрибута <text> x = «NaN» в angularJS nvd3-multi-bar-chart
• 0Присоединение узла к LinkedList
• 1Android — убийство не освобождает память?
• 1Использование понимания вложенного списка для проверки и изменения всех столбцов фрейма данных
• 1Добавить счетчик на панель инструментов только для определенного фрагмента
• 0Строки исчезают на Datatables?
• 1Наблюдаемый список Приведение / Конверсия
• 0AngularJS объем этого в фабрике
• 1Повторите тест на AssertionError
• 1Датагрид привязка МВВМ
• 1C # Формат даты {0: t} — {0: t + 1} 00:00 — 01:00
• 0Форма для разных получателей на основе переключателя ввода
• 01066 — Не уникальный стол / псевдоним: «художники»
• 3Не удалось разрешить com.android.support:support-compat:26.1.0
• 1WebView внутри ViewPager внутри NestedScrollView без вертикальной прокрутки
• 0манипулирование таблицей html с использованием foreach в codeigniter
• 0Руководство Angular.js по запуску теста e2e с использованием транспортира
• 1Назначение и вызов свойств объекта внутри другого объекта
• 1Как я могу получить в своем API дочерний класс?
• 1Захват сообщения окна и затем запуск кода
• 1Сервис автоматического выхода из системы в приложении wpf
• 1Как удалить динамически созданную таблицу в javascript?
• 1Консоли Google Play 100% поэтапное развертывание не совпадают с Full Release?
• 0Поиск галереи jQuery плагин
• 0Почему SetInterval не работает (работает только один раз)
• 1Используйте GetCheckedRadioButtonId () с внешней группой радиосвязи
• 0Перегрузка оператора сложения со многими объектами c ++
• 0Только пользователь тестера может публиковать на своей стене Facebook SDK v4 PHP
• 0Сохранить массив элементов SDL_Surface в классе?
• 0Генерация PDF от AngularJS