List:Community« Previous MessageNext Message »
From:Arjen Lentz Date:March 22 2013 12:20am
Subject:Re: Foreign key on multiple columns
View as plain text  
Hi Norah

> 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)`

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?


Regards,
Arjen.
-- 
Exec.Director @ Open Query (http://openquery.com) MariaDB/MySQL services
Sane business strategy explorations at http://upstarta.com.au
Personal blog at http://lentz.com.au/blog/
Thread
Foreign key on multiple columnsNorah Jones21 Mar
  • Re: Foreign key on multiple columnsArjen Lentz22 Mar