When trying to set a foreign key in PHPMyAdmin, I'm getting "no index defined!" for the column I'm trying to relate to another table.

LB1234
LB1234 used Ask the Experts™
on
Any ideas what the problem could be?  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

Commented:
Uhh, is there an index on the table in the column you're trying to use?  Maybe if you post the CREATE TABLE statement and tell us a little more about the application we could offer more specific suggestions.

Author

Commented:
Hi Ray, there are two columns in question: the user_id column from the users table and a transaction table with a columned called user_id (which I'd like to link to the users table.  I'm see this when i try to use the drop down (giving me a drop down for a table on which I do not want to set up a foreign key "id" and saying "no index defined" for the one on which I do "user_id."

screen shot
Most Valuable Expert 2011
Top Expert 2016
Commented:
Let me suggest a slightly different design that I think will give you some more power and flexibility in your data base.  Create a junction table, something like this:
uid INT NOT NULL DEFAULT 0
tid INT NOT NULL DEFAULT 0
tod TIMESTAMP

Open in new window

The table would be used to relate the users to the transactions, thus creating a many-to-many relationship. The tod column would enable you to see the last time that this user and this transaction interacted.  I think if you give some thought to the SELECT queries you will find that this can create a very efficient reporting design with all kinds of valuable historical data searches.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial