Description:
Hi There,
I have encountered a problem importing data from the SQL Create Script export function within Workbench.
After much debugging I believe I have narrowed it down to be due to how constraints are built into the SQL syntax in this create script.
When I insert this table using phpMyAdmin and the Workbench exported script, it returns this generic error:
-- -----------------------------------------------------
-- Table `zeta`.`member_subscription`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zeta`.`member_subscription` (
`member_subscription_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`member_id` INT( 10 ) UNSIGNED NOT NULL ,
`member_subscription_type_id` INT( 10 ) UNSIGNED NOT NULL ,
`member_subscription_status_id` TINYINT( 4 ) UNSIGNED NOT NULL ,
`expiry_amount` INT( 10 ) NOT NULL ,
`cost` DECIMAL( 10, 2 ) NOT NULL ,
`start_date` DATETIME NOT NULL ,
`end_date` DATETIME NULL ,
`modify_date` TIMESTAMP NULL ,
`creation_date` DATETIME NOT NULL ,
PRIMARY KEY ( `member_subscription_id` ) ,
CONSTRAINT `fk_member_subscriptions_member_subscription_types` FOREIGN KEY ( `member_subscription_type_id` ) REFERENCES `zeta`.`member_subscription_type` (
`member_subscription_type_id`
) ON DELETE NO ACTION ON UPDATE NO ACTION ,
CONSTRAINT `fk_member_subscriptions_members` FOREIGN KEY ( `member_id` ) REFERENCES `zeta`.`member` (
`member_id`
) ON DELETE NO ACTION ON UPDATE NO ACTION ,
CONSTRAINT `fk_member_subscriptions_member_subscription_statuses` FOREIGN KEY ( `member_subscription_status_id` ) REFERENCES `zeta`.`member_subscription_status` (
`member_subscription_status_id`
) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;
MySQL said: Documentation
#1005 - Can't create table '.\zeta\member_subscription.frm' (errno: 150)
However, if I run this:
CREATE TABLE IF NOT EXISTS `zeta`.`catalog_prize` (
`catalog_prize_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`catalog_item_id` INT(10) UNSIGNED NOT NULL ,
`catalog_prize_status_id` TINYINT(4) UNSIGNED NOT NULL ,
`catalog_prize_category_id` INT(10) UNSIGNED NOT NULL ,
`game_id` TINYINT(4) UNSIGNED NOT NULL ,
`name` VARCHAR(65) NOT NULL ,
`short_description` VARCHAR(255) NULL DEFAULT NULL ,
`long_description` TEXT NULL DEFAULT NULL ,
`quantity` SMALLINT(6) NULL DEFAULT 0 ,
`cost` DECIMAL(10,2) NULL DEFAULT 0 ,
`expected_stock_date` DATETIME NULL DEFAULT NULL ,
`modify_date` TIMESTAMP NULL DEFAULT NULL ,
`creation_date` DATETIME NOT NULL ,
PRIMARY KEY (`catalog_prize_id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
CREATE INDEX fk_catalog_prizes_catalog_prize_categories ON `zeta`.`catalog_prize` (`catalog_prize_category_id` ASC) ;
CREATE INDEX fk_catalog_prizes_catalog_prize_statuses ON `zeta`.`catalog_prize` (`catalog_prize_status_id` ASC) ;
CREATE INDEX fk_catalog_prizes_catalog_items ON `zeta`.`catalog_prize` (`catalog_item_id` ASC) ;
CREATE INDEX fk_catalog_prizes_games ON `zeta`.`catalog_prize` (`game_id` ASC) ;
ALTER TABLE `catalog_prize`
ADD CONSTRAINT `fk_catalog_prizes_catalog_prize_categories`
FOREIGN KEY (`catalog_prize_category_id` )
REFERENCES `zeta`.`catalog_prize_category` (`catalog_prize_category_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_catalog_prizes_catalog_prize_statuses`
FOREIGN KEY (`catalog_prize_status_id` )
REFERENCES `zeta`.`catalog_prize_status` (`catalog_prize_status_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_catalog_prizes_catalog_items`
FOREIGN KEY (`catalog_item_id` )
REFERENCES `zeta`.`catalog_item` (`catalog_item_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_catalog_prizes_games`
FOREIGN KEY (`game_id` )
REFERENCES `zeta`.`game` (`game_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Everything is inserted ok - almost as though the process of adding constraints needs to be separated (It would probably be best if creating indexes was done at the bottom of the file too just to keep it grouped nicely).
Could you guys please provide more information on this and if it's something I am doing wrong - or if Workbench could be edited to add constraints as a very last operation (for example, the same way phpMyAdmin exports structure & data; doing constraints last).
How to repeat:
I imagine this is re-creatable by writing any simple relational database with a few tables with foreign key links/relationships and testing export functionality and import into MySQL.
Suggested fix:
As mentioned in description.
Description: Hi There, I have encountered a problem importing data from the SQL Create Script export function within Workbench. After much debugging I believe I have narrowed it down to be due to how constraints are built into the SQL syntax in this create script. When I insert this table using phpMyAdmin and the Workbench exported script, it returns this generic error: -- ----------------------------------------------------- -- Table `zeta`.`member_subscription` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `zeta`.`member_subscription` ( `member_subscription_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT , `member_id` INT( 10 ) UNSIGNED NOT NULL , `member_subscription_type_id` INT( 10 ) UNSIGNED NOT NULL , `member_subscription_status_id` TINYINT( 4 ) UNSIGNED NOT NULL , `expiry_amount` INT( 10 ) NOT NULL , `cost` DECIMAL( 10, 2 ) NOT NULL , `start_date` DATETIME NOT NULL , `end_date` DATETIME NULL , `modify_date` TIMESTAMP NULL , `creation_date` DATETIME NOT NULL , PRIMARY KEY ( `member_subscription_id` ) , CONSTRAINT `fk_member_subscriptions_member_subscription_types` FOREIGN KEY ( `member_subscription_type_id` ) REFERENCES `zeta`.`member_subscription_type` ( `member_subscription_type_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION , CONSTRAINT `fk_member_subscriptions_members` FOREIGN KEY ( `member_id` ) REFERENCES `zeta`.`member` ( `member_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION , CONSTRAINT `fk_member_subscriptions_member_subscription_statuses` FOREIGN KEY ( `member_subscription_status_id` ) REFERENCES `zeta`.`member_subscription_status` ( `member_subscription_status_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci; MySQL said: Documentation #1005 - Can't create table '.\zeta\member_subscription.frm' (errno: 150) However, if I run this: CREATE TABLE IF NOT EXISTS `zeta`.`catalog_prize` ( `catalog_prize_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT , `catalog_item_id` INT(10) UNSIGNED NOT NULL , `catalog_prize_status_id` TINYINT(4) UNSIGNED NOT NULL , `catalog_prize_category_id` INT(10) UNSIGNED NOT NULL , `game_id` TINYINT(4) UNSIGNED NOT NULL , `name` VARCHAR(65) NOT NULL , `short_description` VARCHAR(255) NULL DEFAULT NULL , `long_description` TEXT NULL DEFAULT NULL , `quantity` SMALLINT(6) NULL DEFAULT 0 , `cost` DECIMAL(10,2) NULL DEFAULT 0 , `expected_stock_date` DATETIME NULL DEFAULT NULL , `modify_date` TIMESTAMP NULL DEFAULT NULL , `creation_date` DATETIME NOT NULL , PRIMARY KEY (`catalog_prize_id`) ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci; CREATE INDEX fk_catalog_prizes_catalog_prize_categories ON `zeta`.`catalog_prize` (`catalog_prize_category_id` ASC) ; CREATE INDEX fk_catalog_prizes_catalog_prize_statuses ON `zeta`.`catalog_prize` (`catalog_prize_status_id` ASC) ; CREATE INDEX fk_catalog_prizes_catalog_items ON `zeta`.`catalog_prize` (`catalog_item_id` ASC) ; CREATE INDEX fk_catalog_prizes_games ON `zeta`.`catalog_prize` (`game_id` ASC) ; ALTER TABLE `catalog_prize` ADD CONSTRAINT `fk_catalog_prizes_catalog_prize_categories` FOREIGN KEY (`catalog_prize_category_id` ) REFERENCES `zeta`.`catalog_prize_category` (`catalog_prize_category_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_catalog_prizes_catalog_prize_statuses` FOREIGN KEY (`catalog_prize_status_id` ) REFERENCES `zeta`.`catalog_prize_status` (`catalog_prize_status_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_catalog_prizes_catalog_items` FOREIGN KEY (`catalog_item_id` ) REFERENCES `zeta`.`catalog_item` (`catalog_item_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_catalog_prizes_games` FOREIGN KEY (`game_id` ) REFERENCES `zeta`.`game` (`game_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION; Everything is inserted ok - almost as though the process of adding constraints needs to be separated (It would probably be best if creating indexes was done at the bottom of the file too just to keep it grouped nicely). Could you guys please provide more information on this and if it's something I am doing wrong - or if Workbench could be edited to add constraints as a very last operation (for example, the same way phpMyAdmin exports structure & data; doing constraints last). How to repeat: I imagine this is re-creatable by writing any simple relational database with a few tables with foreign key links/relationships and testing export functionality and import into MySQL. Suggested fix: As mentioned in description.