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

Knipe

@Cybernate: sorry, forgot to specify, ID is the primary key of test1, with the same type as col1 and col2 (INT). There's a bunch of other columns (but that shouldn't matter) AND no other constraints or indices are set. – Knipe

Have you tried this without specifying cascading updates or deletes? – Interrupt

@Thomas: Yes, I tried omitting ON UPDATE and ON DELETE but it does not change the result... – Knipe

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.

Mantelpiece

@Knipe - It works because you have two independent relations to the same parent table column. Think of it this way, only if the parent table key was a multi-column key would you use the syntax you have in your original post. If the parent table column is a single column, then for each child column you want to reference that parent table column, you must create a separate foreign key constraint. – Interrupt

@Thomas: Ok, now I understand! In effect thinking about it, it makes a lot of sense. Thank you. – Knipe

Answer 2

The problem would appear to be that you are specifying the same parent column twice in the same foreign key (i.e, (ID, ID)). The following should work:

Create Table Test1
    (
    PK1 int not null
    , PK2 int not null
    , Primary Key ( PK1, PK2 )
    )

Create Table Test2
    (
    Id int not null Auto_Increment
    , PK1 int not null
    , PK2 int not null
    , Primary Key ( ID )
    , Constraint FK_Test2
        Foreign Key ( PK1, PK2 )
        References Test1( PK1, PK2 )
    )

If it is the case, that you want two columns in a child table referencing the same parent table column, then you must add two foreign key references as shown by rsenna as those represent two independent relations.

Interrupt

Well, but I do not have a composite primary key in Test1... both columns in Test2 should be pointing to the same (unique) primary key in Test1. – Knipe

@Knipe - Then those represent two independent relations and require two foreign key constraints. You would only use the syntax you have in your OP when the parent column is a multi-column key. – Interrupt