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/[email protected]
