List:General Discussion« Previous MessageNext Message »
From:shawn green Date:March 21 2013 4:48pm
Subject:Re: Foreign key on multiple columns
View as plain text  

On 3/21/2013 12:43 PM, Abhishek Choudhary wrote:
>>        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;
>
>
> 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 .
>
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.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Foreign key on multiple columnsNorah Jones21 Mar
  • Re: Foreign key on multiple columnsPeter Brawley21 Mar
    • Re: Foreign key on multiple columnsAbhishek Choudhary21 Mar
      • Re: Foreign key on multiple columnsshawn green21 Mar