I had the same problem and was going crazy,
the ket/foreign key fields must be exactly the same.
I 'forward' engineered the database with MySQL Workbench and was almost
posting a bug!
Cheers
Claudio
2009/3/26 John Daisley <john.dais...@mypostoffice.co.uk>
> Sorry all, I was being a dummy! Missed the unsigned attribute off the
> foreign key columns on the problem tables.
>
> Regards
>
>
> > Hi,
> >
> > I have the script below to create 5 tables. Three of them create fine but
> > two return an error of 150 which I understand to be a foreign key issue,
> > however I can't see anything wrong with the foreign key statements.
> >
> > Could someone possibly have a look and see if they can identify the issue
> > please? I think I have been looking at it so long now 'I can't see the
> > wood for the trees'.
> >
> > The two tables which fail are `cube_security` and `cube_measures`. Im
> > running MySQL 5.1.32 on a Windows XP test machine.
> >
> > --------SCRIPT----------
> > 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 `cubedoc` DEFAULT CHARACTER SET latin1
> COLLATE
> > latin1_general_ci ;
> > SHOW WARNINGS;
> > USE `cubedoc`;
> >
> > -- -----------------------------------------------------
> > -- Table `cubedoc`.`cubes`
> > -- -----------------------------------------------------
> > DROP TABLE IF EXISTS `cubedoc`.`cubes` ;
> >
> > SHOW WARNINGS;
> > CREATE TABLE IF NOT EXISTS `cubedoc`.`cubes` (
> > `idcubes` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
> > `cube_name` CHAR(30) NOT NULL ,
> > `cube_kit_location` VARCHAR(100) NOT NULL DEFAULT 'On Development Pc' ,
> > `Developer` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
> > Dando','Will Morley') NOT NULL ,
> > PRIMARY KEY (`idcubes`) )
> > ENGINE = InnoDB
> > COMMENT = 'General Cube Data';
> >
> > SHOW WARNINGS;
> >
> > -- -----------------------------------------------------
> > -- Table `cubedoc`.`cube_dimensions`
> > -- -----------------------------------------------------
> > DROP TABLE IF EXISTS `cubedoc`.`cube_dimensions` ;
> >
> > SHOW WARNINGS;
> > CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_dimensions` (
> > `idcube_dimensions` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
> > `idcubes` SMALLINT UNSIGNED NOT NULL ,
> > `dimension_name` CHAR(30) NOT NULL ,
> > `level_name` CHAR(30) NOT NULL ,
> > PRIMARY KEY (`idcube_dimensions`) ,
> > INDEX `dimensions_cubes_fk` (`idcubes` ASC) ,
> > CONSTRAINT `dimensions_cubes_fk`
> > FOREIGN KEY (`idcubes` )
> > REFERENCES `cubedoc`.`cubes` (`idcubes` )
> > ON DELETE NO ACTION
> > ON UPDATE CASCADE)
> > ENGINE = InnoDB
> > COMMENT = 'Cube Dimension Data ';
> >
> > SHOW WARNINGS;
> >
> > -- -----------------------------------------------------
> > -- Table `cubedoc`.`cube_security`
> > -- -----------------------------------------------------
> > DROP TABLE IF EXISTS `cubedoc`.`cube_security` ;
> >
> > SHOW WARNINGS;
> > CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_security` (
> > `idcube_security` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
> > `idcubes` SMALLINT NOT NULL ,
> > `dimension` CHAR(30) NOT NULL ,
> > `level_name` CHAR(30) NOT NULL ,
> > `restricted_user_group` CHAR(30) NOT NULL ,
> > `restriction_details` TEXT NOT NULL ,
> > PRIMARY KEY (`idcube_security`) ,
> > INDEX `security_idcubes_fk` (`idcubes` ASC) ,
> > CONSTRAINT `security_idcubes_fk`
> > FOREIGN KEY (`idcubes` )
> > REFERENCES `cubedoc`.`cubes` (`idcubes` )
> > ON DELETE NO ACTION
> > ON UPDATE CASCADE)
> > ENGINE = InnoDB
> > COMMENT = 'Internal Cube Security';
> >
> > SHOW WARNINGS;
> >
> > -- -----------------------------------------------------
> > -- Table `cubedoc`.`cube_measures`
> > -- -----------------------------------------------------
> > DROP TABLE IF EXISTS `cubedoc`.`cube_measures` ;
> >
> > SHOW WARNINGS;
> > CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_measures` (
> > `idcube_measures` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
> > `idcubes` SMALLINT NOT NULL ,
> > `measure_name` CHAR(30) NOT NULL ,
> > `measure_source_calculation` VARCHAR(80) NOT NULL ,
> > PRIMARY KEY (`idcube_measures`) ,
> > INDEX `measures_idcubes_fk` (`idcubes` ASC) ,
> > CONSTRAINT `measures_idcubes_fk`
> > FOREIGN KEY (`idcubes` )
> > REFERENCES `cubedoc`.`cubes` (`idcubes` )
> > ON DELETE NO ACTION
> > ON UPDATE CASCADE)
> > ENGINE = InnoDB
> > COMMENT = 'Cube Measure Data';
> >
> > SHOW WARNINGS;
> >
> > -- -----------------------------------------------------
> > -- Table `cubedoc`.`cube_changelog`
> > -- -----------------------------------------------------
> > DROP TABLE IF EXISTS `cubedoc`.`cube_changelog` ;
> >
> > SHOW WARNINGS;
> > CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_changelog` (
> > `idcube_changelog` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
> > `idcubes` SMALLINT UNSIGNED NOT NULL ,
> > `change_date` DATE NOT NULL ,
> > `version` DECIMAL(4,2) NOT NULL ,
> > `status` ENUM('Development','User Acceptance','Live','Retired') NOT
> NULL
> > DEFAULT 'Development' ,
> > `change_call_work_request_no` INT NOT NULL ,
> > `change_detail` TEXT NOT NULL ,
> > `actioned_by` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
> > Dando','Will Morley') NOT NULL ,
> > PRIMARY KEY (`idcube_changelog`) ,
> > INDEX `changelog_idcubes_fk` (`idcubes` ASC) ,
> > CONSTRAINT `changelog_idcubes_fk`
> > FOREIGN KEY (`idcubes` )
> > REFERENCES `cubedoc`.`cubes` (`idcubes` )
> > ON DELETE NO ACTION
> > ON UPDATE CASCADE)
> > ENGINE = InnoDB
> > COMMENT = 'Cube Changes log';
> >
> > SHOW WARNINGS;
> >
> > SET sql_mo...@old_sql_mode;
> > SET foreign_key_chec...@old_foreign_key_checks;
> > SET unique_chec...@old_unique_checks;
> >
> > --------END SCRIPT----------
> >
> > TIA
> > John
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk
> >
> >
> > ______________________________________________
> > This email has been scanned by Netintelligence
> > http://www.netintelligence.com/email
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
>
>
