What is wrong with this Query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dhui
Forum Newbie
Posts: 14
Joined: Mon May 10, 2010 1:42 pm

What is wrong with this Query?

Post 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!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: What is wrong with this Query?

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: What is wrong with this Query?

Post 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.
Post Reply