List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 7 2006 3:16pm
Subject:Re: Can't Create Foreign Key Constraints
View as plain text  
Lola,

/>And got this following error message:

 >ERROR 1072 (42000): Key column '(not null)' doesn't exist in table
/
Yes, it's telling you what;s wrong: to define a constraint on a key, the 
table def must first define the key.

PB

-----


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?
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.2/251 - Release Date: 2/4/2006
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