CODEWITHSUNDEEP
mysqlforeign-keysddl
user3352330
user3352330

Reputation:

Multiple columns of a table refer to same foreign key column

I am having a table named subcategories whose records could further be linked to other records in the table. So I have created a subcategorylinker table which defines these links. In this table I have 2 columns subcategory and nextsubcategory both of which refer to id field of subcategories table. But I am getting an error during table creation. The error is as given below:

ERROR 1005: Can't create table 'test01.subcategorylinker' (errno: 150)

Table definition is as follows:

CREATE  TABLE `test01`.`subcategorylinker` (    
  `id` INT NOT NULL ,            
  `subcategory` INT NOT NULL ,    
  `nextsubcategory` INT NOT NULL ,    
  PRIMARY KEY (`id`) ,    
  INDEX `fk_subcategorylinker_sub` (`subcategory` ASC, `nextsubcategory` ASC) ,    
  CONSTRAINT `fk_subcategorylinker_sub`    
    FOREIGN KEY (`subcategory` , `nextsubcategory` )    
    REFERENCES `test01`.`subcategories` (`id` , `id` )    
    ON DELETE NO ACTION    
    ON UPDATE NO ACTION);

Upvotes: 2

Views: 4370

Answers (1)

Mureinik
Mureinik

Reputation: 311393

Lets examine the foreign key clause:

FOREIGN KEY (`subcategory` , `nextsubcategory` )

REFERENCES `test01`.`subcategories` (`id` , `id` )

This means that every combination of (subcategory, nextsubcategroy) in your table should match a combination of (id, id) in the table. Since there is no such thing as a combination of (id, id), as it's a single field, this statement is illegal.

Instead, you could have two foreign keys, on for subcategory and one for nextsubcategory, each of which should match an id:

CREATE  TABLE `test01`.`subcategorylinker` (
  `id` INT NOT NULL ,
  `subcategory` INT NOT NULL ,
  `nextsubcategory` INT NOT NULL ,

  PRIMARY KEY (`id`) ,

  INDEX `fk_subcategorylinker_sub` (`subcategory` ASC),

  CONSTRAINT `fk_subcategorylinker_sub`
    FOREIGN KEY (`subcategory`)
    REFERENCES `test01`.`subcategories` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

  INDEX `fk_nextsubcategorylinker_sub` (`nextsubcategory` ASC),

  CONSTRAINT `fk_nextsubcategorylinker_sub`
    FOREIGN KEY (`nextsubcategory`)
    REFERENCES `test01`.`subcategories` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Upvotes: 1

Related Questions