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?

--
Lola - mailto:lola (AT) his (DOT) com
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

Lola J. Lee Beno 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>.

2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films it is an
INT. The manual says

Corresponding columns in the foreign key and the referenced key must have
similar internal data types inside InnoDB so that they can be compared
without a type conversion. The size and sign of integer types must be the
same.

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."

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

Michael Stassen wrote:

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.



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.


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/In...nstraints.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 (AT) his (DOT) com
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

Peter Brawley wrote:
Looking over the script again, RatingID is being defined, first in
FilmsRatings which I ran to create this table and then in Films, the
create table script which didn't work.


How do I make the table def first define the key explicitly? Or am I
missing something obvious?


--
Lola - mailto:lola (AT) his (DOT) com
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

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/...ate-table.html



Lola J. Lee Beno wrote:

--
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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

Gleb Paharenko wrote:


Thanks - just what I needed. Looks like I'll need to be extra careful
with sql scripts generated from Mysql Workbench, which is still alpha
right now.


--
Lola - mailto:lola (AT) his (DOT) com
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw

Hello.

You can post a bug:
http://dev.mysql.com/doc/refman/5.0/en/bug-reports.html

Lola J. Lee Beno wrote:

--
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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw