Description:
MWB allows a designer to create relationships between tables when the data type for the columns is not exactly the same. For example, it creates a relationship between an "INT UNSIGNED NOT NULL" and "INT NOT NULL". It will also create relationships between "INT UNSIGNED NOT NULL" and "SMALLINT UNSIGNED NOT NULL".
Of course, the SQL generated from these relationships results in an Errno:150 in MySQL.
See these two table definitions:
"orders.signaling_partner_id" has a relationship to "signaling_parter.signaling_partner_id" that should not be allowed since the data types don't match (smallint and int).
CREATE TABLE IF NOT EXISTS `coma`.`orders` (
`orders_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`order_number` CHAR(15) NOT NULL ,
`version` TINYINT UNSIGNED NOT NULL DEFAULT 0 ,
`order_service_type` CHAR(4) NOT NULL ,
`order_status_id` INT UNSIGNED NOT NULL ,
`nd_order_status_id` INT UNSIGNED NOT NULL ,
`company_id` INT UNSIGNED NOT NULL ,
`signaling_partner_id` SMALLINT UNSIGNED NOT NULL ,
`order_type_id` INT UNSIGNED NOT NULL ,
`modified` TIMESTAMP NOT NULL ,
`created` TIMESTAMP NULL ,
PRIMARY KEY (`orders_id`, `nd_order_status_id`, `company_id`, `order_status_id`, `signaling_partner_id`, `order_type_id`) ,
INDEX fk_orders_nd_order_status (`nd_order_status_id` ASC) ,
INDEX fk_orders_company (`company_id` ASC) ,
INDEX fk_orders_order_status (`order_status_id` ASC) ,
INDEX fk_orders_signaling_partner (`signaling_partner_id` ASC) ,
INDEX fk_orders_order_type (`order_type_id` ASC) ,
CONSTRAINT `fk_orders_nd_order_status`
FOREIGN KEY (`nd_order_status_id` )
REFERENCES `coma`.`nd_order_status` (`nd_order_status_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_company`
FOREIGN KEY (`company_id` )
REFERENCES `coma`.`company` (`company_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_order_status`
FOREIGN KEY (`order_status_id` )
REFERENCES `coma`.`order_status` (`order_status_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_signaling_partner`
FOREIGN KEY (`signaling_partner_id` )
REFERENCES `coma`.`signaling_partner` (`signaling_partner_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_order_type`
FOREIGN KEY (`order_type_id` )
REFERENCES `coma`.`order_type` (`order_type_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-------------------------------------------------------------------------
-------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `coma`.`signaling_partner` (
`signaling_partner_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(35) NOT NULL ,
`sos_company_id_number` SMALLINT UNSIGNED NOT NULL ,
PRIMARY KEY (`signaling_partner_id`) )
ENGINE = InnoDB;
How to repeat:
Create InnoDB tables with relationships between incorrect data types.
Suggested fix:
MWB should prevent a user from creating these invalid relationships. It should cause an alert warning about the problem.
Description: MWB allows a designer to create relationships between tables when the data type for the columns is not exactly the same. For example, it creates a relationship between an "INT UNSIGNED NOT NULL" and "INT NOT NULL". It will also create relationships between "INT UNSIGNED NOT NULL" and "SMALLINT UNSIGNED NOT NULL". Of course, the SQL generated from these relationships results in an Errno:150 in MySQL. See these two table definitions: "orders.signaling_partner_id" has a relationship to "signaling_parter.signaling_partner_id" that should not be allowed since the data types don't match (smallint and int). CREATE TABLE IF NOT EXISTS `coma`.`orders` ( `orders_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `order_number` CHAR(15) NOT NULL , `version` TINYINT UNSIGNED NOT NULL DEFAULT 0 , `order_service_type` CHAR(4) NOT NULL , `order_status_id` INT UNSIGNED NOT NULL , `nd_order_status_id` INT UNSIGNED NOT NULL , `company_id` INT UNSIGNED NOT NULL , `signaling_partner_id` SMALLINT UNSIGNED NOT NULL , `order_type_id` INT UNSIGNED NOT NULL , `modified` TIMESTAMP NOT NULL , `created` TIMESTAMP NULL , PRIMARY KEY (`orders_id`, `nd_order_status_id`, `company_id`, `order_status_id`, `signaling_partner_id`, `order_type_id`) , INDEX fk_orders_nd_order_status (`nd_order_status_id` ASC) , INDEX fk_orders_company (`company_id` ASC) , INDEX fk_orders_order_status (`order_status_id` ASC) , INDEX fk_orders_signaling_partner (`signaling_partner_id` ASC) , INDEX fk_orders_order_type (`order_type_id` ASC) , CONSTRAINT `fk_orders_nd_order_status` FOREIGN KEY (`nd_order_status_id` ) REFERENCES `coma`.`nd_order_status` (`nd_order_status_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_orders_company` FOREIGN KEY (`company_id` ) REFERENCES `coma`.`company` (`company_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_orders_order_status` FOREIGN KEY (`order_status_id` ) REFERENCES `coma`.`order_status` (`order_status_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_orders_signaling_partner` FOREIGN KEY (`signaling_partner_id` ) REFERENCES `coma`.`signaling_partner` (`signaling_partner_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_orders_order_type` FOREIGN KEY (`order_type_id` ) REFERENCES `coma`.`order_type` (`order_type_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION) ENGINE = InnoDB; ------------------------------------------------------------------------- ------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `coma`.`signaling_partner` ( `signaling_partner_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(35) NOT NULL , `sos_company_id_number` SMALLINT UNSIGNED NOT NULL , PRIMARY KEY (`signaling_partner_id`) ) ENGINE = InnoDB; How to repeat: Create InnoDB tables with relationships between incorrect data types. Suggested fix: MWB should prevent a user from creating these invalid relationships. It should cause an alert warning about the problem.