0

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
Improve this question
1
  • Not yet, I'm using the mysql workbench. Time to hit up ssh. I'll let you know what i find. Commented May 17, 2011 at 3:02

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

That's some really great input... I didn't know a primary key couldn't be null, it does make sense though. It looks like mysql workbench made all the foreign keys primary, when editing the relationship. It seems I can't undo that.
This was the problem! I changed the keys to Not Null, and the ON DELETE to No Action. Why is there a "set null" if a foreign key has to be not null?
Foreign key column can be null, but if it's a part of primary then it cannot be null. I agree, mysql may generate a better error message in such cases
For some reason workbench is forcing the foreign keys to by primary. I will look into this, perhaps there's something amiss.
You are definitely missing something. In mysql foreign key automatically creates an index on this column, but it doesn't have to be a primary index
0

Uhm, I may be very wrong, but are you allowed to have more than one Primary Key? I thought the whole purpose was that it was indeed a Primary (Unique) Key.

Improve this answer

2 Comments

Yes, you can combine columns to create a composite primary key
The idEmployee is listed under the UNIQUE index. Is this not where it's suppose to go?
0

Adding to a1ex07's reply, see this thread:

http://forums.mysql.com/read.php?22,19755,259716#msg-259716

It might also be due to signed/unsigned integer-related problems.

Also, your primary key (a unique, not null (set of) field(s)) is, de facto, your employee ID, based on the table's definition.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.