While adding foreign key relation mysql (5.5.29) gives ERROR 1050:

Question

While adding foreign key relation MySQL gives ERROR 1050:

I have 2 tables and trying to give foreign key relation with other, but it gives below error.

ERROR 1005: Can't create table 'yellowbikes.#sql-1e8_82' (errno: 121)

SQL Statement:

ALTER TABLE `yellowbikes`.`schedule` 
  ADD CONSTRAINT `bike_number`
  FOREIGN KEY (`bike_number` )
  REFERENCES `yellowbikes`.`bike` (`bike_number` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `bike_number_idx` (`bike_number` ASC)

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'schedule' already exists

SQL Statement:

CREATE TABLE `schedule` (
   `bikeid` int(11) NOT NULL,
   `bike_number` varchar(24) NOT NULL,
   PRIMARY KEY (`bikeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Are you 100% sure you dont have a table with the name schedule? Try DROP TABLE IF EXISTS schedule before your second statement. — Alex
Hi Alex,no there is no separate table as 'schedule', also already tried with some different name, stil the same issue — Parab

Vinayak Pahalwan Vinayak Pahalwan · Accepted Answer · 2013-01-23T08:42:20

Check to ensure they are both the same type.Schedule is having type utf8. Check whthr yellowbikes is of type default ?!?! If so, change it to utf8

Edit:

Also you can check if you MySql DB is MyISAM. If so, chane it to InnoDB because if you need the database to enforce foreign key constraints, or you need the database to handle the changes made by set of DML operations as single unit of work then you would choose InnoDB over MyISAM, since these features are absent from the MyISAM engine.

While adding foreign key relation mysql (5.5.29) gives ERROR 1050:

1 Answers