top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Foreign key on multiple columns

0 votes
314 views

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)

posted Mar 21, 2013 by Salil Agrawal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

4 Answers

+1 vote
 
Best answer

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 Mar 21, 2013 by anonymous
0 votes

Dupe reference column.

answer Mar 21, 2013 by anonymous
0 votes

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 Mar 21, 2013 by anonymous
0 votes

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?

answer Mar 22, 2013 by anonymous
Similar Questions
0 votes

I have a table which looks like this:

answer_id  q_id  answer  qscore_id  answer_timestamp
1          10    Male    3          **********
2          10    Male    3          **********
3          11    Male    3          **********
4          10    Male    3          **********
5          11    Male    3          **********
6          12    Male    3          **********
7          11    Male    3          **********

So I have multiple answers for the same questions (q_id). I want to be able to retrieve only ONE answer per question and that be the most recent answer.
There should be THREE rows returned, which are all the most recent answered for that q_id:

4          10    Male    3          **********
6          12    Male    3          **********
7          11    Male    3          **********
0 votes

I am running My-SQL in cluster mode with two machine. Want to know if mysql database get corrupted on one of the machine will it force the corruption on the other machine too or in this case sync between two mysql instances will stop after the corruption.

0 votes

To source sqldump i can use the source command, but if I need to do the same stuff using command line without going to the sqlpromt, can I achieve that.

0 votes

My site is using mysql and PHP, now for the scale purpose want to introduce mysql-cluster. Few questions are -
1. Do I need to change any code which is written in PHP.
2. What are the steps to convert mysql to mysql-cluster.

Appreciate the help.

+1 vote

I am using PHP along with mysql. Mysql default configuration allows to have 100 simultaneous connection which I want to chane to 200. Please help.

...