List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 7 2006 2:11pm
Subject:Re: Can't Create Foreign Key Constraints
View as plain text  
Lola J. Lee Beno wrote:
> I'm trying to create foreign key constraints and keep getting an error 
> message 1005 (I did look it up, but didn't see an obvious solution to 
> fixing this for my database).
> 
> The version I'm using is 5.0.17-max.  I used Mysql WorkBench to create 
> the database schema and had it generate the sql script.
> 
> I created a table as such:
> 
> CREATE TABLE `ows`.`FilmsRatings` (
>   `RatingID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
>   `Rating` VARCHAR(50) NULL,
>   PRIMARY KEY (`RatingID`)
> )
> ENGINE = InnoDB
> CHARACTER SET utf8 COLLATE utf8_general_ci;
> 
> Then, I created another table as such:
> 
> CREATE TABLE `ows`.`Films` (
>   `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
>   `MovieTitle` TEXT NULL,
>   `PitchText` TEXT NULL,
>   `AmountBudgeted` DECIMAL(11, 0) NULL,
>   `RatingID` INT(11) NULL,
>   `Summary` LONGTEXT NULL,
>   `ImageName` VARCHAR(50) NULL,
>   `DateInTheaters` DATETIME NULL,
>   PRIMARY KEY (`FilmID`),
>   CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`)
>     REFERENCES `ows`.`FilmsRatings` (`RatingID`)
>     ON DELETE CASCADE
>     ON UPDATE CASCADE
> )
> ENGINE = InnoDB
> CHARACTER SET utf8 COLLATE utf8_general_ci;
> 
> And got this following error message:
> 
> ERROR 1072 (42000): Key column '(not null)' doesn't exist in table
> 
> I tried again, this time deleting the CONSTRAINTs details.  Then I tried 
> to alter the table as such:
> 
> mysql> alter table films
>     -> add constraint fk_films_ratings
>     -> foreign key (RatingID) references FilmsRatings (RatingID);
> 
> Which produced this error message:
> 
> ERROR 1005 (HY000): Can't create table './ows/#sql-a8_11.frm' (errno: 150)
> mysql> drop table films;
> 
> This database is being run with InnoDB engine, so I should be able to 
> create the foreign key constraints.  So why is this happening?

1) I'm not sure what you are intending with "(`(not null)`)" in the middle of 
your foreign key definition, but that isn't valid mysql syntax.  See the manual 
for the correct syntax 
<http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html>.

2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films it is an 
INT.  The manual says

    Corresponding columns in the foreign key and the referenced key must have
    similar internal data types inside InnoDB so that they can be compared
    without a type conversion. The size and sign of integer types must be the
    same.

3) Again quoting the manual, "You can use SHOW ENGINE INNODB  STATUS to display 
a detailed explanation of the most  recent InnoDB foreign key error in the  server."

Michael
Thread
Can't Create Foreign Key ConstraintsLola J. Lee Beno7 Feb
  • Re: Can't Create Foreign Key ConstraintsMichael Stassen7 Feb
    • Re: Can't Create Foreign Key ConstraintsLola J. Lee Beno7 Feb
      • Re: Can't Create Foreign Key ConstraintsGleb Paharenko7 Feb
        • Re: Can't Create Foreign Key ConstraintsLola J. Lee Beno7 Feb
          • Re: Can't Create Foreign Key ConstraintsGleb Paharenko7 Feb
  • Re: Can't Create Foreign Key ConstraintsPeter Brawley7 Feb
    • Re: Can't Create Foreign Key ConstraintsLola J. Lee Beno7 Feb