-1

This is a long question please read through. I have listed some of the links I have referred, but I have read all the suggestion thrown up while posting this question. This is not a duplicate question as marked here https://stackoverflow.com/questions/31341481/unable-to-figure-out-the-cause-for-error-1005-in-mysql-database.

I've been trying to write a small MySQL database, which contains two tables role and user defined as follows:

role

CREATE TABLE `role` (
  `roleid` varchar(20) NOT NULL,
  `role_name` varchar(255) NOT NULL,
  `permission` int(11) NOT NULL,
  PRIMARY KEY (`roleid`),
  UNIQUE KEY `roleid` (`roleid`),
  UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

user

CREATE TABLE `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `rid` varchar(20) NOT NULL,
  UNIQUE KEY `uid` (`uid`),
  UNIQUE KEY `uname` (`uname`),
  UNIQUE KEY `slug` (`slug`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `password` (`password`),
  KEY `rid` (`rid`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `role` (`roleid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So now when I issue the ALTER TABLE 'user' DROP FOREIGN KEY;

I get an error:

ERROR 1005 (HY000): Can't create table 'parth.#sql-418_24' (errno: 150)

parth is the name of the database.

I've consulted following discussions:

The SHOW ENGINE INNODB STATUS gave the following output regarding the above situation:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
150710 18:20:35 Error in foreign key constraint of table parth/#sql-418_24:
foreign key:
Syntax error close to:

Please help me figure this out. Thanks.

The table parth/#sql-418_24 generated automatically when I executed the ALTER TABLE command. According to the reference manual ALTER TABLE works by copying the contents of the original table into a temporary table, which in this case is #sql-418_24 which is then renamed to the name of the table as specified in the schema. So it doesn't seem like a problem with special characters in table name. Please help.

MySql Version 5.5.43-0ubuntu0.14.04.1 operating system Ubuntu 14.04

Thanks for helping.

Community
  • 1
  • 1
Abhishek Singh
  • 27
  • 6

1 Answers1

0

try this ... 

ALTER TABLE `user`  DROP FOREIGN KEY `user_ibfk_1`;

you are supposed to drop a constraint, here the constraint is named user_ibfk_1

Lalit Mehra
  • 1,183
  • 1
  • 13
  • 33
  • ALTER TABLE table_name DROP FOREIGN KEY constraint_name is the statement used to drop a foreign key. You have to specify a constraint name in this case. If you omit it, MySQL generates a constraint name for you. – Lalit Mehra Jul 12 '15 at 15:27
  • @abhishek would you pkease mark it as the correct answer ... and you are welcome. Happy to help. – Lalit Mehra Jul 12 '15 at 19:46