List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:March 26 2009 11:05am
Subject:Re: SOLVED: Re: Foreign Key Issue
View as plain text  
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.daisley@stripped>

> 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_MODE=@OLD_SQL_MODE;
> > SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
> > SET UNIQUE_CHECKS=@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=1
> >
> >
> > ______________________________________________
> > 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=1
>
>

Thread
SOLVED: Re: Foreign Key IssueJohn Daisley26 Mar
  • Re: SOLVED: Re: Foreign Key IssueClaudio Nanni26 Mar