Hi,
I have been using MySQL Workbench to create a database to index my music collection, i keep running in to an error on forward enginering with the mdbmusic.band-genres.
I have tried the code in PHPMyAdmin and i am getting the same error, i can't see a problem in the SQL code.
Does anybody know what is going on?
The 'mdbmusic.band-genres' Code:
Code:
-- -----------------------------------------------------
-- Table `mdbmusic`.`band-genres`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mdbmusic`.`band-genres` (
`fk-band_id` INT NOT NULL ,
`fk-genre_id` INT NOT NULL ,
PRIMARY KEY (`fk-band_id`, `fk-genre_id`) ,
INDEX `fk-band_id` (`fk-band_id` ASC) ,
INDEX `fk-genre_id` (`fk-genre_id` ASC) ,
CONSTRAINT `fk-band_id`
FOREIGN KEY (`fk-band_id` )
REFERENCES `mdbmusic`.`band` (`band_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk-genre_id`
FOREIGN KEY (`fk-genre_id` )
REFERENCES `mdbmusic`.`genre` (`genre_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
The Full SQL Code:
Code:
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';
CREATE SCHEMA IF NOT EXISTS `mdbmain` ;
CREATE SCHEMA IF NOT EXISTS `mdbmusic` ;
USE `mdbmain` ;
-- -----------------------------------------------------
-- Table `mdbmain`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mdbmain`.`users` (
`users_id` INT NOT NULL AUTO_INCREMENT ,
`users_username` VARCHAR(30) NOT NULL ,
`users_password` VARCHAR(32) NOT NULL ,
`users_email` TEXT NOT NULL ,
PRIMARY KEY (`users_id`) ,
UNIQUE INDEX `mdb-users_username_UNIQUE` (`users_username` ASC) )
ENGINE = InnoDB;
USE `mdbmusic` ;
-- -----------------------------------------------------
-- Table `mdbmusic`.`band`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mdbmusic`.`band` (
`band_id` INT NOT NULL AUTO_INCREMENT ,
`band_name` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`band_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mdbmusic`.`genre`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mdbmusic`.`genre` (
`genre_id` INT NOT NULL AUTO_INCREMENT ,
`genre_name` VARCHAR(255) NOT NULL ,
`genre_desc` TEXT NULL ,
PRIMARY KEY (`genre_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mdbmusic`.`album`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mdbmusic`.`album` (
`album_id` INT NOT NULL AUTO_INCREMENT ,
`album_title` VARCHAR(255) NOT NULL ,
`album_various` ENUM('0','1') NOT NULL COMMENT '0=no\n1=yes' ,
PRIMARY KEY (`album_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mdbmusic`.`people`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mdbmusic`.`people` (
`people_id` INT NOT NULL AUTO_INCREMENT ,
`people_name` VARCHAR(255) NOT NULL ,
PRIMARY KEY (`people_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mdbmusic`.`band-members`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mdbmusic`.`band-members` (
`fk-band_id` INT NOT NULL ,
`fk-people_id` INT NOT NULL ,
PRIMARY KEY (`fk-band_id`, `fk-people_id`) ,
INDEX `fk-people_id` (`fk-people_id` ASC) ,
INDEX `fk-band_id` (`fk-band_id` ASC) ,
CONSTRAINT `fk-band_id`
FOREIGN KEY (`fk-band_id` )
REFERENCES `mdbmusic`.`band` (`band_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk-people_id`
FOREIGN KEY (`fk-people_id` )
REFERENCES `mdbmusic`.`people` (`people_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mdbmusic`.`band-genres`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mdbmusic`.`band-genres` (
`fk-band_id` INT NOT NULL ,
`fk-genre_id` INT NOT NULL ,
PRIMARY KEY (`fk-band_id`, `fk-genre_id`) ,
INDEX `fk-band_id` (`fk-band_id` ASC) ,
INDEX `fk-genre_id` (`fk-genre_id` ASC) ,
CONSTRAINT `fk-band_id`
FOREIGN KEY (`fk-band_id` )
REFERENCES `mdbmusic`.`band` (`band_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk-genre_id`
FOREIGN KEY (`fk-genre_id` )
REFERENCES `mdbmusic`.`genre` (`genre_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;