Question
When trying to forward engineer my database into mysql database I am getting the error, ERROR: Error 1215: Cannot add foreign key constraint. Here is
When trying to forward engineer my database into mysql database I am getting the error, ERROR: Error 1215: Cannot add foreign key constraint.
Here is the script that is trying to run:
- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- ----------------------------------------------------- -- Schema mydb -- -----------------------------------------------------
-- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ; USE `mydb` ;
-- ----------------------------------------------------- -- Table `mydb`.`warranty` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`warranty` ;
CREATE TABLE IF NOT EXISTS `mydb`.`warranty` ( `warranty_id` VARCHAR(255) NOT NULL, `warranty_number` VARCHAR(25) NOT NULL COMMENT 'Warranty Number', `warranty_effective_date` DATETIME NOT NULL COMMENT 'Warranty Effective Date', `payment_option` VARCHAR(100) NOT NULL COMMENT 'Pay monthly or annunally, or all at once', `total_amount` DOUBLE NOT NULL COMMENT 'Total Warranty Amount ', `active` TINYINT(1) NOT NULL, `date_created` DATETIME NOT NULL, `additional_information` LONGTEXT NULL COMMENT 'Additional information for finance, and car dealership. Will not be seen by customer', PRIMARY KEY (`warranty_id`)) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `mydb`.`warranty_edit` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`warranty_edit` ;
CREATE TABLE IF NOT EXISTS `mydb`.`warranty_edit` ( `warranty_edit_id` VARCHAR(255) NOT NULL, `fk_warranty_id_3` VARCHAR(255) NOT NULL COMMENT 'Foreign Key to warranty table', `edited_table_name` VARCHAR(45) NOT NULL COMMENT 'Name of the table that was updated', `edited_date` DATETIME NOT NULL COMMENT 'When record is inserted into DB', `edited_by` VARCHAR(45) NOT NULL COMMENT 'Who updated the records', `additional_information` LONGTEXT NULL COMMENT 'Extra information if needed', PRIMARY KEY (`warranty_edit_id`), INDEX `policy_id_idx` (`fk_warranty_id_3` ASC), CONSTRAINT `warranty_id` FOREIGN KEY (`fk_warranty_id_3`) REFERENCES `mydb`.`warranty` (`warranty_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `mydb`.`bill` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`bill` ;
CREATE TABLE IF NOT EXISTS `mydb`.`bill` ( `bill_id` VARCHAR(255) NOT NULL, `fk_warranty_id_1` VARCHAR(255) NOT NULL COMMENT 'Foreign Key of bill table', `due_date` DATETIME NOT NULL COMMENT 'Payment due date', `minimum_payment` DOUBLE NOT NULL COMMENT 'Minimum payment or per bill payment', `created_date` DATETIME NOT NULL COMMENT 'Bill generated date', `balance` DOUBLE NOT NULL COMMENT 'Customer makes partial payment, this has remaining balance', `status` VARCHAR(45) NOT NULL COMMENT 'Paid, or Unpaid', PRIMARY KEY (`bill_id`), INDEX `warranty_id_idx` (`fk_warranty_id_1` ASC), CONSTRAINT `warranty_id` FOREIGN KEY (`fk_warranty_id_1`) REFERENCES `mydb`.`warranty` (`warranty_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `mydb`.`vehicle` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`vehicle` ;
CREATE TABLE IF NOT EXISTS `mydb`.`vehicle` ( `vehicle_id` VARCHAR(255) NOT NULL COMMENT 'Foreign Key to Warranty Table', `fk_warranty_id_2` VARCHAR(255) NOT NULL, `year` CHAR(4) NOT NULL COMMENT 'Year of Car', `make` VARCHAR(45) NOT NULL, `model` VARCHAR(45) NOT NULL COMMENT 'Model', `color` VARCHAR(45) NULL COMMENT 'Color', `trim` VARCHAR(45) NULL COMMENT 'Trim', `milage` INT NOT NULL COMMENT 'Mileage', `vin_number` VARCHAR(20) NOT NULL COMMENT 'VIN Number', `vehicle_plate_number` VARCHAR(20) NOT NULL COMMENT 'Registered Plate', `vehicle_registered_state` VARCHAR(45) NOT NULL COMMENT 'Registered State', `aftermarket_parts` VARCHAR(45) NULL COMMENT 'After Market Parts Not Included (Information)', `created_date` DATETIME NOT NULL COMMENT 'Created Date', PRIMARY KEY (`vehicle_id`), INDEX `warranty_id_idx` (`fk_warranty_id_2` ASC), CONSTRAINT `warranty_id` FOREIGN KEY (`fk_warranty_id_2`) REFERENCES `mydb`.`warranty` (`warranty_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `mydb`.`coverage` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`coverage` ;
CREATE TABLE IF NOT EXISTS `mydb`.`coverage` ( `coverage_id` VARCHAR(255) NOT NULL, `coverage_package` VARCHAR(45) NOT NULL COMMENT 'Name of the package selected', `fk_vehicle_id` VARCHAR(255) NOT NULL, `detuctible` FLOAT NOT NULL, `terms` VARCHAR(45) NOT NULL, PRIMARY KEY (`coverage_id`), INDEX `vehicle_id_idx` (`fk_vehicle_id` ASC), CONSTRAINT `vehicle_id` FOREIGN KEY (`fk_vehicle_id`) REFERENCES `mydb`.`vehicle` (`vehicle_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `mydb`.`warranty_coverage` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`warranty_coverage` ;
CREATE TABLE IF NOT EXISTS `mydb`.`warranty_coverage` ( `warranty_coverage_id` VARCHAR(255) NOT NULL, `fk_warranty_id_4` VARCHAR(255) NOT NULL, `fk_coverage_id` VARCHAR(255) NOT NULL, `active` TINYINT(1) NOT NULL, PRIMARY KEY (`warranty_coverage_id`), INDEX `warranty_id_idx` (`fk_warranty_id_4` ASC), INDEX `coverage_id_idx` (`fk_coverage_id` ASC), CONSTRAINT `warranty_id` FOREIGN KEY (`fk_warranty_id_4`) REFERENCES `mydb`.`warranty` (`warranty_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `coverage_id` FOREIGN KEY (`fk_coverage_id`) REFERENCES `mydb`.`coverage` (`coverage_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ----------------------------------------------------- -- Table `mydb`.`payment` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`payment` ;
CREATE TABLE IF NOT EXISTS `mydb`.`payment` ( `payment_id` VARCHAR(255) NOT NULL, `fk_bill_id` VARCHAR(255) NOT NULL COMMENT 'Foreign Key to bill table', `paid_date` DATETIME NOT NULL COMMENT 'Payment made date', `amount` DOUBLE NOT NULL COMMENT 'How much is paid', `payment_method` VARCHAR(100) NOT NULL COMMENT 'Credit / Debit / Check / E-Withdrawl', `payer_first_name` VARCHAR(45) NULL, `payer_last_name` VARCHAR(45) NULL COMMENT 'Payer Last Name', `card_number` VARCHAR(45) NULL COMMENT 'Credit or Debit Card Number', `zip_code` VARCHAR(10) NULL, `card_expiration_date` VARCHAR(10) NULL COMMENT 'Expiration Date of Credit Card', `card_type` VARCHAR(25) NULL COMMENT 'Visa, Mastercard, American Express, Discover, other', `debit_or_credit` VARCHAR(45) NULL COMMENT 'Is the card debit or credit', `bank_name` VARCHAR(100) NULL COMMENT 'Bank Name for E-Withdrawal', `account_number` VARCHAR(20) NULL COMMENT 'Account Number of Payer', `routing_number` VARCHAR(20) NULL COMMENT 'Bank Routing Number', `check_number` VARCHAR(20) NULL, `addition_information` LONGTEXT NULL, `created_date` DATETIME NOT NULL COMMENT 'When the payment is created', PRIMARY KEY (`payment_id`), INDEX `bill_id_idx` (`fk_bill_id` ASC), CONSTRAINT `bill_id` FOREIGN KEY (`fk_bill_id`) REFERENCES `mydb`.`bill` (`bill_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB COMMENT = ' ';
SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started