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=arch...@jab.org
