I get the error #1005 - Can't create table 'db1.#sql-9e8_2c1' (errno: 150) (<a href="server_engines.php?engine=InnoDB&amp;page=Status&amp;token=3947cba14f7c39305d6f460b76698c5e">Details...</a>)

I have 2 tables that I want to connect as a 1-1 relationship.
table1: a ,b ,c ,d
table2: a ,b

the plan is that table2 will hold the keys of some of the records that are in table1..sort of table1 - all the items, table2 - all the free items. this will allow me to walkthrough table2 and know what items in table1 are free (the data in table 2 is static and table1 is changing all the time)

so I'm trying to add to table2 2 foreign keys A and B from table1 I tried to do it in PhpMyAdmin and by SQL but it gives me that 1005 error.
the thing is, when I'm on the page where you see a dropdown of all the keys u can connect to, I don't see table1.B I do see table1.A though.
same with other tables I wanted to connect to table1..field B just not in that list!!!!

I created table1 atleast 10 times already, both of the fields are primary keys in both of the tables, I'm losing my minnnnnnnnnnnnd!!

Re: foreign key problem 80 80

have you added composite key on column A and B ?

Re: foreign key problem 80 80

alright, it seems to have something to do with indeces which I don't know much about..am I supposed to add an index to every key field?

it works now, don't know if that's how it's supposed to be though..

Re: foreign key problem 80 80

Yes, you need an index on any referenced field.

Re: foreign key problem 80 80

thank you

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.