CODEWITHSUNDEEP
mysql
shelzmike
shelzmike

Reputation: 173

MySQL Workbench, Newbie to DB, Constraint Problems - Error 1005, erno 121

Okay, I understand what the problem is, I sort of understand constraints in that they need to be unique in the db (didn't really know that before now, so I learned something. Not sure what they are for at the moment, but can learn later. My problem is, I think a simple matter of not understanding how MySQL Workbench assigns constraints. Apparently, you can set it in options to do so automatically which I think I am setup to do; however, something that I did made many constraints the same because they used the same FK, here is an example of what I mean: Table 1:

    CONSTRAINT `dish_type`
    FOREIGN KEY (`dish_type` )
    REFERENCES `acs_operations_dev`.`dish_types` (`id` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `tech_number`
    FOREIGN KEY (`tech_number` )
    REFERENCES `acs_operations_dev`.`employees` (`tech_number` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)

Table 2

PRIMARY KEY (`id`) ,
  INDEX `tech_number` (`tech_number` ASC) ,
  INDEX `car` (`car` ASC) ,
  INDEX `field_dev_coach` (`field_dev_coach` ASC) ,
  UNIQUE INDEX `etad_user_UNIQUE` (`etad_user` ASC) ,
  CONSTRAINT `tech_number`
    FOREIGN KEY (`tech_number` )
    REFERENCES `acs_operations_dev`.`employees` (`tech_number` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `car`
    FOREIGN KEY (`car` )
    REFERENCES `acs_operations_dev`.`cars` (`id` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,

I left out some parts - just trying to show the problem - i.e. on tech_number.

I created the tables manually, on the model page (i.e. I did not use the EER "drawing" style). When I created the FKs, here is what I have done:

MySQL Workbench - FK Creation Example

What is it that I am doing here that is causing WB to not auto-assign constraints? Also, what can I do to go back and fix it within WB.

I hope that makes sense. I used this method for every table, btw - so it is consistent across the board. Thanks!

Mike

Upvotes: 0

Views: 811

Answers (1)

Stephen Senkomago Musoke
Stephen Senkomago Musoke

Reputation: 3523

  1. Check that the tables are InnoDB

  2. Check that the columns for the foreign key have the same data type. If you are using integer columns check that both are either unsigned or signed.

  3. Check the names of the foreign keys they have to be unqiue. I usually use the following fromat fk_currenttablename_foreignkeytablename

Upvotes: 1

Related Questions