List:General Discussion« Previous MessageNext Message »
From:Lola J. Lee Beno Date:February 7 2006 2:25pm
Subject:Re: Can't Create Foreign Key Constraints
View as plain text  
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?


-- 
Lola - mailto:lola@stripped
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
Freedom is not free.
I'm in Bowie, MD, USA, halfway between DC and Annapolis.
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