Hello.
The query which is works is:
CREATE TABLE `Films` (
`FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`MovieTitle` TEXT NULL
,`PitchText` TEXT NULL,
`AmountBudgeted` DECIMAL(11, 0) NULL,
`RatingID` INT(11) unsigned ,
`Summary` LONGTEXT NULL,
`ImageName` VARCHAR(50) NULL,
`DateInTheaters` DATETIME NULL,
PRIMARY KEY (`FilmID`),
CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (RatingID)
REFERENCES `FilmsRatings` (`RatingID`) ON DELETE CASCADE
ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE
utf8_general_ci;
See:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
Lola J. Lee Beno wrote:
> Michael Stassen wrote:
>
>> 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>.
>
>
>
> This is from the script that was generated using Mysql Workbench,
> 1.0.3-alpha. I tried it with (null) and (not null); neither worked.
>
>
>> 2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films
>> it is an INT. The manual says
>>
>
>
> I then modified the query 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) UNSIGNED 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;
>
> No dice.
>
>> 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."
>
>
>
> Which gives me:
>
> LATEST FOREIGN KEY ERROR
> ------------------------
> 060207 8:33:49 Error in foreign key constraint of table ows/#sql-a8_11:
>
> foreign key (RatingID) references FilmsRatings (RatingID):
> Cannot find an index in the referenced table where the
> referenced columns appear as the first columns, or column types
> in the table and the referenced table do not match for constraint.
> Note that the internal storage type of ENUM and SET changed in
> tables created with >= InnoDB-4.1.12, and such columns in old tables
> cannot be referenced by such columns in new tables.
> See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
> for correct foreign key definition.
>
>
> Which leads me back to the same URL that you gave me. so, it looks like
> I should create an index for FilmsRatings first, and then create the
> table Films - is that correct?
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
