Bug #52248 relationship based on existing column creates invalid foreign key

• Submitted: 21 Mar 2010 3:23
• Modified: 21 Apr 2010 13:45
• Reporter: [ name withheld ]
• Status: No Feedback
• Category: MySQL Workbench: Modeling
• Severity: S3 (Non-critical)
• Version: 5.2.16
• OS: Any
• CPU Architecture: Any

[21 Mar 2010 3:23] [ name withheld ]

Description:
Using a primary key that's an INT UNSIGNED from an existing table as a foreign key for another creates a foreign key that doesn't preserve the UNSIGNED attribute.

How to repeat:
1. Create a model that has the following table:

create table foo_t (
  `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

2. Create a new table bar_t.

3. Add a column to bar_t that will be a foreign key to foo_t.id (place a new 1:n non-identifying relationship, select bar_t to receive foreign key, select foo_t to pick foo_t.id).

The column created in bar_t will be an INT(5), but it will not be UNSIGNED, which guarantees an when this schema is created.

[21 Mar 2010 13:45] Valeriy Kravchuk

What OS are you working on? I can not repeat this with 5.2.16 on Mac OS X. This is how foreign key table is created when I try to follow your descrition:

-- -----------------------------------------------------
-- Table `mydb`.`table3`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table3` (
  `table1_id` INT(5) UNSIGNED NOT NULL ,
  INDEX `fk_table3_table11` (`table1_id` ASC) ,
  CONSTRAINT `fk_table3_table11`
    FOREIGN KEY (`table1_id` )
    REFERENCES `mydb`.`table1` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

So, UNSIGNED property is inherited for me. Can you provide .mwb file with exact table to refer to with a foreign key?

[21 Apr 2010 23:02] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".