[SOLVED] problem in creating foreign key

[phplearner2008]

Hi,

I am a mysql newbie. I have two tables

profile and interest for which i want to create foreign key relationship.

 

profile table has following fields

( id int(10) not null primary key

email varchar(30) not null unique key

password varchar(20) not null

name varchar(20) )

 

interest table has following fields

( sent_by int(10) not null primary key

  sent_to int(10) not null primary key

  status varchar(10) not null )

 

I tried to create foreign key relationship like this

ALTER TABLE interest ADD FOREIGN KEY(sent_by,sent_to)

REFERENCES profile(id) ON DELETE CASCADE ON UPDATE CASCADE

 

But I got error code: 1005 Cannot create table'\.#sql778_1.frm'(errno.150)

 

The profile table has only one primary key that is 'id'.

The interest table has combination of columns sent_by and sent_to as primary keys. How do i create foreign key relationship. Can anyone guide me?

Thanks

[Daniel0]

Try to make two separate constraints, i.e.

 

ALTER TABLE interest ADD FOREIGN KEY(sent_by)
REFERENCES profile(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE interest ADD FOREIGN KEY(sent_to)
REFERENCES profile(id) ON DELETE CASCADE ON UPDATE CASCADE;

[phplearner2008]

Thanks Daniel. It works now!

[Daniel0]

The reason why it didn't work for you was that you were trying to make a constraint where sent_to AND sent_by equals id in profile. That'll only be true if sent_to=sent_by, which it never should, thus it fails.