Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
dhui
Forum Newbie
Posts: 14 Joined: Mon May 10, 2010 1:42 pm
Post
by dhui » Fri Jun 18, 2010 9:04 am
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!
Eran
DevNet Master
Posts: 3549 Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME
Post
by Eran » Fri Jun 18, 2010 9:13 am
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
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Fri Jun 18, 2010 11:32 am
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.