MySQL foreign key creation with alter table command
- by user313338
I created some tables using MySQL Workbench, and then did forward ‘forward engineer’ to create scripts to create these tables. BUT, the scripts lead me to a number of problems. One of which involves the foreign keys. So I tried creating separate foreign key additions using alter table and I am still getting problems. The code is below (the set statements, drop/create statements I left in … though I don’t think they should matter for this):
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';
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
-- -----------------------------------------------------
-- Table `mydb`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`User` ;
CREATE TABLE IF NOT EXISTS `mydb`.`User` (
`UserName` VARCHAR(35) NOT NULL ,
`Num_Accts` INT NOT NULL ,
`Password` VARCHAR(45) NULL ,
`Email` VARCHAR(45) NULL ,
`User_ID` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`User_ID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`User_Space`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`User_Space` ;
CREATE TABLE IF NOT EXISTS `mydb`.`User_Space` (
`User_UserName` VARCHAR(35) NOT NULL ,
`User_Space_ID` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`User_Space_ID`),
FOREIGN KEY (`User_UserName`)
REFERENCES `mydb`.`User` (`UserName`)
ON UPDATE CASCADE ON DELETE CASCADE)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
The error this produces is:
Error Code: 1005 Can't create table 'mydb.user_space' (errno: 150)
Anybody know what the heck I’m doing wrong?? And anybody else have problems with the script generation done by mysql workbench? It’s a nice tool, but annoying that it pumps out scripts that don’t work for me.
[As an fyi here’s the script it auto-generates:
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';
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
-- -----------------------------------------------------
-- Table `mydb`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`User` ;
CREATE TABLE IF NOT EXISTS `mydb`.`User` (
`UserName` VARCHAR(35) NOT NULL ,
`Num_Accts` INT NOT NULL ,
`Password` VARCHAR(45) NULL ,
`Email` VARCHAR(45) NULL ,
`User_ID` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`User_ID`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`User_Space`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`User_Space` ;
CREATE TABLE IF NOT EXISTS `mydb`.`User_Space` (
`User_Space_ID` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`User_Space_ID`) ,
INDEX `User_ID` () ,
CONSTRAINT `User_ID`
FOREIGN KEY ()
REFERENCES `mydb`.`User` ()
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
**
Thanks!]
