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
