List:General Discussion« Previous MessageNext Message »
From:John Daisley Date:March 26 2009 10:51am
Subject:SOLVED: Re: Foreign Key Issue
View as plain text  
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
>
>


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