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?
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
