List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:February 7 2006 3:29pm
Subject:Re: Can't Create Foreign Key Constraints
View as plain text  
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
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com
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