Search code examples
mysqlcreate-table

Create table - Mysql

I'm looking for a little help with this table. First: The table isn't being accepted by mysql. I'm getting a 150 error, which doesn't tell me much. Second, I'm very certain that I'm not using the best data types & way to go about things.... please give me a little input! 

CREATE  TABLE IF NOT EXISTS `axis`.`Employee` (
`idEmployee` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`idAccount` INT(10) UNSIGNED NOT NULL ,
`user` VARCHAR(45) NULL DEFAULT NULL ,
`pass` VARCHAR(45) NULL DEFAULT NULL ,
`firstName` VARCHAR(45) NULL DEFAULT NULL ,
`lastName` VARCHAR(45) NULL DEFAULT NULL ,
`middleName` VARCHAR(45) NULL DEFAULT NULL ,
`idGrp` INT(10) UNSIGNED NULL DEFAULT NULL ,
`idCompany` INT(10) UNSIGNED NULL DEFAULT NULL ,
`idLocation` INT(10) UNSIGNED NULL DEFAULT NULL ,
`idUnit` INT(10) UNSIGNED NULL DEFAULT NULL ,
`idCrew` INT(10) UNSIGNED NULL DEFAULT NULL ,
`idPosition` INT(10) UNSIGNED NULL DEFAULT NULL ,
`officeLoc` VARCHAR(45) NULL DEFAULT NULL ,
`wPhone` VARCHAR(12) NULL DEFAULT NULL ,
`wCell` VARCHAR(12) NULL DEFAULT NULL ,
`wFax` VARCHAR(12) NULL DEFAULT NULL ,
`wEmail` VARCHAR(45) NULL DEFAULT NULL ,
`radio` VARCHAR(12) NULL DEFAULT NULL ,
`hPhone` VARCHAR(12) NULL DEFAULT NULL ,
`hCell` VARCHAR(12) NULL DEFAULT NULL ,
`hEmail` VARCHAR(45) NULL DEFAULT NULL ,
`addrOne` VARCHAR(45) NULL DEFAULT NULL ,
`addrTwo` VARCHAR(45) NULL DEFAULT NULL ,
`city` VARCHAR(45) NULL DEFAULT NULL ,
`state` VARCHAR(45) NULL DEFAULT NULL ,
`zip` VARCHAR(10) NULL DEFAULT NULL ,
`emergContact` VARCHAR(45) NULL DEFAULT NULL ,
`emergPhone` VARCHAR(12) NULL DEFAULT NULL ,
`gender` ENUM('male','female') NULL DEFAULT NULL ,
`birthDate` DATE NULL DEFAULT NULL ,
`ssn` VARCHAR(5) NULL DEFAULT NULL ,
`hireDate` DATE NULL DEFAULT NULL ,
`sepDate` DATE NULL DEFAULT NULL ,
`comment` VARCHAR(255) NULL DEFAULT NULL ,
`photo` MEDIUMBLOB NULL DEFAULT NULL ,
`isActive` BIT(1) NOT NULL DEFAULT 1 ,
`allowLogin` BIT(1) NOT NULL DEFAULT 0 ,
`trackTravel` BIT(1) NOT NULL DEFAULT 1 ,
`trackTimesheet` BIT(1) NOT NULL DEFAULT 1 ,
`defFltArr` VARCHAR(10) NULL DEFAULT NULL ,
`defFltDep` VARCHAR(10) NULL DEFAULT NULL ,
`defDayTimeStart` TIME NOT NULL DEFAULT '06:00:00' ,
`defHoursPerDay` DECIMAL(4,2) NOT NULL DEFAULT '11.50' ,
`userType` ENUM('root','admin','view','acl','report','employee') NOT NULL DEFAULT 'employee',
`created` DATETIME NOT NULL ,
`modified` TIMESTAMP NOT NULL ,
`modifiedBy` INT(10) UNSIGNED NULL DEFAULT NULL ,
PRIMARY KEY (`idEmployee`, `idAccount`, `idGrp`, `idCompany`, `idLocation`, `idUnit`, `idCrew`, `idPosition`, `modifiedBy`) ,
UNIQUE INDEX `UNIQUE` (`idEmployee` ASC) ,
INDEX `fk_Employee_Grp` (`idGrp` ASC) ,
INDEX `fk_Employee_Company` (`idCompany` ASC) ,
INDEX `fk_Employee_Unit` (`idUnit` ASC) ,
INDEX `fk_Employee_Location` (`idLocation` ASC) ,
INDEX `fk_Employee_Crew` (`idCrew` ASC) ,
INDEX `fk_Employee_Position` (`idPosition` ASC) ,
INDEX `fk_Employee_Employee` (`modifiedBy` ASC) ,
CONSTRAINT `fk_Employee_Grp`
FOREIGN KEY (`idGrp` )
REFERENCES `axis`.`Grp` (`idGrp` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_Employee_Company`
FOREIGN KEY (`idCompany` )
REFERENCES `axis`.`Company` (`idCompany` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_Employee_Unit`
FOREIGN KEY (`idUnit` )
REFERENCES `axis`.`Unit` (`idUnit` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_Employee_Location`
FOREIGN KEY (`idLocation` )
REFERENCES `axis`.`Location` (`idLocation` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_Employee_Crew`
FOREIGN KEY (`idCrew` )
REFERENCES `axis`.`Crew` (`idCrew` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_Employee_Position`
FOREIGN KEY (`idPosition` )
REFERENCES `axis`.`Position` (`idPosition` )
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT `fk_Employee_Employee`
FOREIGN KEY (`modifiedBy` )
REFERENCES `axis`.`Employee` (`idEmployee` )
ON DELETE SET NULL
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
Solution

  • One of many mistakes :

    `idGrp` INT(10) UNSIGNED NULL DEFAULT NULL`

    then

    PRIMARY KEY (`idEmployee`, `idAccount`, `idGrp`, ... )

    (Column that is part of PRIMARY KEY must be NOT NULL - mysql silently makes it not null, finally 

    CONSTRAINT `fk_Employee_Grp` FOREIGN KEY (`idGrp` ) 
 REFERENCES `axis`.`Grp` (`idGrp` ) ON DELETE SET NULL ON UPDATE CASCADE

    (which is impossible - ON DELETE SET NULL - column is not null.

    And why do you create composite key at all? auto_increment column uniquely identifies your record. All other stuff is redundant. It might possible make sense in some cases for MyIsam tables (I would not recommend to use it anyway), because a value for auto_increment column will be generated differently if it's a part of composite index , but not for INNODB engine.