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.

Any ideas what the problem could be?  Thanks.
LVL 1
LB1234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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.
0
LB1234Author 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
0
Ray PaseurCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.