Foreign key on multiple columns

Question

I'm trying to create a foreign key on two columns but getting error...

Here's what I tried:

CREATE TABLE test2 (
  ID INT NOT NULL AUTO_INCREMENT,  
  col1 INT NOT NULL,
  col2 INT NOT NULL, 
  PRIMARY KEY (ID),
  CONSTRAINT fk FOREIGN KEY (col1, col2)
                REFERENCES test1(ID, ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT
) ENGINE=InnoDB;

But I get ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)

Answer 1

Another solution would be to make two FK declarations, one for each column.

 CREATE TABLE test2 (
 ID INT NOT NULL AUTO_INCREMENT,
 col1 INT NOT NULL,
 col2 INT NOT NULL,
 PRIMARY KEY (ID),
 CONSTRAINT fk FOREIGN KEY (col1)
 REFERENCES test1(ID)
 ON UPDATE CASCADE
 ON DELETE RESTRICT ,
 CONSTRAINT fk2 FOREIGN KEY (col2)
 REFERENCES test1(ID)
 ON UPDATE CASCADE
 ON DELETE RESTRICT
 ) ENGINE=InnoDB;

This is the preferred syntax and it meets your original intent of associating both col1 and col2 to the ID column of the other table.

Answer 2

Dupe reference column.

Answer 3

I think error is because of referencing the same column in test1 table (ID,ID) . try to change the column name then run the code hope ur problem will solve out .

Answer 4

Setting up foreign keys can be quite finicky, also depending on your version of MySQL.
Do you have an index on (col1,col2) in the referenced table?