Database import situation

Question

Mysql doesn't want to add this database into my localhost database section.

Am I doing something wrong?

db.sql This tutorial: https://github.com/samanz/cakecart

Error:

SQL query:

CREATE TABLE `categories` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 50 ) NULL default NULL ,
`parent_id` INT( 11 ) UNSIGNED default '0',
`order` INT( 3 ) default '0',
`image` VARCHAR( 50 ) NULL default NULL ,
`ids` VARCHAR( 225 ) NULL default NULL ,
`url` VARCHAR( 255 ) NULL default NULL ,
PRIMARY KEY ( `id` ) ,
FOREIGN KEY ( `parent_id` ) REFERENCES categories( `id` ) ,
UNIQUE KEY `url` ( `url` )
);

MySQL said: Documentation
#1005 - Can't create table 'cake_cart.categories' (errno: 150)

Possible duplicate. http://stackoverflow.com/questions/1749332/mysql-cant-create-table-errno-150 — Tony Hopkinson, Mar 26 '12 at 14:41

Brian · Answer 1 · 2012-03-26T18:57:51.810

2

Error 150 is a foreign key problem. Likely caused by:

FOREIGN KEY ( `parent_id` ) REFERENCES categories( `id` ) ,

You can't make a "foreign" key reference to the same table you're creating. Simply make the parent_id column indexed instead.

KEY `parent_id` ( `parent_id` ) ,

edited Mar 26 '12 at 18:57

answered Mar 26 '12 at 14:41

Brian

3,013
19
27

rackemup420 · Answer 2 · 2012-03-26T14:56:04.650

1

Should look something like this...

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `parent_id` int(11) unsigned NOT NULL DEFAULT '0',
  `order` int(3) NOT NULL DEFAULT '0',
  `img` varchar(50) NOT NULL,
  `ids` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I updated the structure and ran it on my database and it worked.

edited Mar 26 '12 at 14:56

answered Mar 26 '12 at 14:40

rackemup420

1,600
2
15
37

Actually you don't want to just remove that foreign key since he's planning to join onto the table using that column. You'll want to make sure it's indexed with a normal key statement instead as I mentioned below. Your solution will create the table, yes, but it will also cause his joins to get much slower much more quickly as the table fills with data. – Brian Mar 26 '12 at 14:43
That line works, try to import the whole database into your localhost quick, then tell me if you get my same error. – Dale Mar 26 '12 at 14:44
oops didnt see i forgot to put that in. Thank you for pointing that out, i jsut went to my phpmyadmin and created it quickly :D. Updated! – rackemup420 Mar 26 '12 at 14:47

Database import situation

2 Answers2