Page 1 of 1

What is wrong with this Query?

Posted: Fri Jun 18, 2010 9:04 am
by dhui
What is wrong with my query below? I keep getting an error but I dont really understand why...

Code: Select all

CREATE TABLE fruits
(
apples BIGINT NOT NULL,
bananas BIGINT NOT NULL,
oranges VARCHAR(255) NOT NULL,
PRIMARY KEY  (apples, bananas, oranges),
KEY fruits_oranges(oranges),
CONSTRAINT fruits_apples FOREIGN KEY (apples) REFERENCES produce (apples) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fruits_bananas FOREIGN KEY (bananas) REFERENCES produce (bananas) ON DELETE CASCADE ON UPDATE CASCADE								
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
[Err] 1005 - Can't create table 'mediarecall_jobs.fruits' (errno: 150)

Any help would be appreciated!

Re: What is wrong with this Query?

Posted: Fri Jun 18, 2010 9:13 am
by Eran
Error 150 means a problem with foreign key constraints. Check the referenced table (produce) and see that it exists and has the relevant columns (which should be unique). If that doesn't help, create the table without the constraints and then try to add them one by one to see the specific problems they create

Re: What is wrong with this Query?

Posted: Fri Jun 18, 2010 11:32 am
by Weirdan
pytrin wrote:Check the referenced table (produce) and see that it exists and has the relevant columns (which should be unique).
Also, both ends of constraint have to be of the same type (BIGINT in your case), and be indexed.