By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,996 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,996 IT Pros & Developers. It's quick & easy.

error 150: Can't create table

P: 7
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
Expand|Select|Wrap|Line Numbers
  1. CREATE  TABLE `shoppingcart`.`sub_categories` (
  2.  
  3.   `sub_category_id` INT NOT NULL ,
  4.  
  5.   `parent_category_id` INT NOT NULL ,
  6.  
  7.   PRIMARY KEY (`sub_category_id`) ,
  8.  
  9.   INDEX `sub_categories_categories` (`sub_category_id` ASC, `parent_category_id` ASC) ,
  10.  
  11.   CONSTRAINT `sub_categories_categories`
  12.  
  13.     FOREIGN KEY (`sub_category_id` , `parent_category_id` )
  14.  
  15.     REFERENCES `shoppingcart`.`categories` (`id` , `id` )
  16.  
  17.     ON DELETE NO ACTION
  18.  
  19.     ON UPDATE CASCADE)
  20.  
  21. ENGINE = InnoDB
  22.  
  23.  
  24.  
So , what's the wrong ? am using MySQL 5.0.7
May 28 '12 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,033
You need and index on categoried like this:
Expand|Select|Wrap|Line Numbers
  1.  ALTER TABLE `test`.`categories` ADD INDEX (`id`, `id`);
But that is not allowed, because it produces this error:
Expand|Select|Wrap|Line Numbers
  1. ERROR 1060 (42S21): Duplicate column name 'id'
May 28 '12 #2

P: 7
@Luuk
So ? actually i hear this before on Official MySQL Forums but without any benefit !
May 28 '12 #3

Expert 100+
P: 1,033
I think you need to do it this way:
Expand|Select|Wrap|Line Numbers
  1. CREATE  TABLE `test`.`sub_categories` ( 
  2.   `sub_category_id` INT NOT NULL , 
  3.   `parent_category_id` INT NOT NULL , 
  4.   PRIMARY KEY (`sub_category_id`,`parent_category_id`) , 
  5.   INDEX `sub_categories_categories` (`sub_category_id` ASC, `parent_category_id` ASC) , 
  6.   CONSTRAINT `sub_categories_categories` 
  7.     FOREIGN KEY (`sub_category_id` ) 
  8.     REFERENCES `test`.`categories` (`id` ) 
  9.     ON DELETE NO ACTION 
  10.     ON UPDATE CASCADE,
  11.   CONSTRAINT `parent_categories_categories` 
  12.     FOREIGN KEY (`parent_category_id` ) 
  13.     REFERENCES `test`.`categories` (`id` ) 
  14.     ON DELETE NO ACTION 
  15.     ON UPDATE CASCADE) 
  16. ENGINE = InnoDB 
  17.  
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 ;)
May 28 '12 #4

P: 7
It doesn't work ! Unfo.
May 28 '12 #5

Expert 100+
P: 1,033
I should have copied this: (especially when i noted the type on previous response.....)

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `sub_categories` (
  2.   `sub_category_id` int(11) NOT NULL,
  3.   `parent_category_id` int(11) NOT NULL,
  4.   PRIMARY KEY (`sub_category_id`,`parent_category_id`),
  5.   KEY `sub_categories_categories` (`sub_category_id`,`parent_category_id`),
  6.   KEY `parent_categories_categories` (`parent_category_id`),
  7.   CONSTRAINT `parent_categories_categories` 
  8.     FOREIGN KEY (`parent_category_id`) 
  9.     REFERENCES `categories` (`id`) 
  10.     ON DELETE NO ACTION 
  11.     ON UPDATE CASCADE,
  12.   CONSTRAINT `sub_categories_categories` 
  13.     FOREIGN KEY (`sub_category_id`) 
  14.     REFERENCES `categories` (`id`) 
  15.     ON DELETE NO ACTION 
  16.     ON UPDATE CASCADE
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
May 28 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.