phisherpryce
asked on
Incorrect table definition; there can be only one auto column and it must be defined as a key
I am attempting to migrate from MS SQL to MYSQL, using the MYSQL Migration Toolkit. I have generated scripts with the tool that I am running from myPHPadmin. While some of the CREATE TABLE commands are working, a few of them generate this error:
Incorrect table definition; there can be only one auto column and it must be defined as a key.
My understanding is you cannot have more than one field with auto_increment, and it must be primary key. Can you not have multiple primary keys but set only one of the to auto_increment?
Incorrect table definition; there can be only one auto column and it must be defined as a key.
My understanding is you cannot have more than one field with auto_increment, and it must be primary key. Can you not have multiple primary keys but set only one of the to auto_increment?
DROP TABLE IF EXISTS `captain1`.`tblEducation`;
CREATE TABLE `captain1`.`tblEducation` (
`UserID` INT(10) NOT NULL,
`EducationID` INT(10) NOT NULL AUTO_INCREMENT,
`InstituteName` VARCHAR(50) NULL,
`ProgramName` VARCHAR(50) NULL,
`LevelObtained` VARCHAR(50) NULL,
`StudyFrom` DATETIME NULL,
`StudyTo` DATETIME NULL,
`GradDate` DATETIME NULL,
`Skills` VARCHAR(50) NULL,
`Type` VARCHAR(50) NULL,
PRIMARY KEY (`UserID`, `EducationID`),
CONSTRAINT `FK_tblEducation_tblUsers` FOREIGN KEY `FK_tblEducation_tblUsers` (`UserID`)
REFERENCES `captain1`.`tblUsers` (`UserID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = INNODB;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.