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