error 150: Can't create table

[Hatem Salem]

Hi

Am trying to create a table called (sub_categories). This table have two columns :

sub_category_id: Primary (int)
parent_category id: not null (int)

Both tables reference the column (id) from categories table. When i try to creating the table it gives me the error :

ERROR 1005: Can't create table 'shoppingcart.sub_categories' (errno: 150)

Here is the SQL syntax

CREATE  TABLE `shoppingcart`.`sub_categories` (
 
  `sub_category_id` INT NOT NULL ,
 
  `parent_category_id` INT NOT NULL ,
 
  PRIMARY KEY (`sub_category_id`) ,
 
  INDEX `sub_categories_categories` (`sub_category_id` ASC, `parent_category_id` ASC) ,
 
  CONSTRAINT `sub_categories_categories`
 
    FOREIGN KEY (`sub_category_id` , `parent_category_id` )
 
    REFERENCES `shoppingcart`.`categories` (`id` , `id` )
 
    ON DELETE NO ACTION
 
    ON UPDATE CASCADE)
 
ENGINE = InnoDB
 
 
 

So , what's the wrong ? am using MySQL 5.0.7

[Luuk]

You need and index on categoried like this:

 ALTER TABLE `test`.`categories` ADD INDEX (`id`, `id`);

But that is not allowed, because it produces this error:

ERROR 1060 (42S21): Duplicate column name 'id'

[Hatem Salem]

@Luuk
So ? actually i hear this before on Official MySQL Forums but without any benefit !

[Luuk]

I think you need to do it this way:

CREATE  TABLE `test`.`sub_categories` ( 
  `sub_category_id` INT NOT NULL , 
  `parent_category_id` INT NOT NULL , 
  PRIMARY KEY (`sub_category_id`,`parent_category_id`) , 
  INDEX `sub_categories_categories` (`sub_category_id` ASC, `parent_category_id` ASC) , 
  CONSTRAINT `sub_categories_categories` 
    FOREIGN KEY (`sub_category_id` ) 
    REFERENCES `test`.`categories` (`id` ) 
    ON DELETE NO ACTION 
    ON UPDATE CASCADE,
  CONSTRAINT `parent_categories_categories` 
    FOREIGN KEY (`parent_category_id` ) 
    REFERENCES `test`.`categories` (`id` ) 
    ON DELETE NO ACTION 
    ON UPDATE CASCADE) 
ENGINE = InnoDB 
 

But, of course, i'm not sure because i dont have enough info on what you exacly want to do, and my experiance with foreign key is not that deep ;)

[Hatem Salem]

It doesn't work ! Unfo.

[Luuk]

I should have copied this: (especially when i noted the type on previous response.....)

CREATE TABLE `sub_categories` (
  `sub_category_id` int(11) NOT NULL,
  `parent_category_id` int(11) NOT NULL,
  PRIMARY KEY (`sub_category_id`,`parent_category_id`),
  KEY `sub_categories_categories` (`sub_category_id`,`parent_category_id`),
  KEY `parent_categories_categories` (`parent_category_id`),
  CONSTRAINT `parent_categories_categories` 
    FOREIGN KEY (`parent_category_id`) 
    REFERENCES `categories` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE CASCADE,
  CONSTRAINT `sub_categories_categories` 
    FOREIGN KEY (`sub_category_id`) 
    REFERENCES `categories` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8