Neebur Neebur - 21 days ago 7
MySQL Question

Cannot add foreign key SQL fiddle

I'm having issues with the foriegn key in my table. If any can point out the error, please do, I have no idea what it is.

CREATE TABLE IF NOT EXISTS `user_account` (
`accountID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL,
`typeID` int(10) UNSIGNED NOT NULL,
`email` VARCHAR(100) NOT NULL,
`password` VARCHAR(100) NOT NULL,
PRIMARY KEY (`accountID`),
FOREIGN KEY (`typeID`) REFERENCES account_type(`typeID`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `account_type`(
`typeID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`typeName` VARCHAR (20) NOT NULL,
`description` VARCHAR (255),
PRIMARY KEY (`typeID`),
) ENGINE = InnoDB;


As far as I'm concerned, my datatypes are fine.

Thanks for the help.

Answer

Just do them in the opposite order after you fix the TYPO extra comma in the first table below.

CREATE TABLE IF NOT EXISTS `account_type`(
  `typeID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `typeName` VARCHAR (20) NOT NULL,
  `description` VARCHAR (255),
  PRIMARY KEY (`typeID`)
) ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `user_account` (
  `accountID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
  `username` VARCHAR(20) NOT NULL,
  `typeID` int(10) UNSIGNED NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`accountID`),
  FOREIGN KEY (`typeID`) REFERENCES account_type(`typeID`)
) ENGINE = InnoDB;
Comments