Adding foreign key on multiple columns

Question

I'm trying to create a foreign key on two columns of a table to point to the same column of another table, but I seem to get an error...

Here's what I do:

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)

If I only have one column, however, the table is correctly created.

Could someone point out to me where the error is?

Thanks n

Answer 1

Tried it here and got the same error. This works though:

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

Yes, I know - your script should work (even if it doesn't seem to make much sense). Yet, I guess this new version is better.