I'm creating a database for a class project, and I have a 'User' table with a column called 'email', which is the primary key for the table. Oh yeah, and all my tables are InnoDB tables.

I also have a few tables with foreign keys that refer to this field of the 'User' table.

I've created all my tables and I'm now adding the FOREIGN KEY constraints.

So, I typed the following into phpMyAdmin:

But I get the following error whenever I try to add a foreign key that references 'User' (doesn't matter what table the foreign key is in...this happens for all of them:

This was odd, I thought...since all the other foreign keys were created just fine.

First, after changing the name of the table from User to GenUser, I stopped getting that error, but I got a different error. This one said basically that I had no index for 'user_email'...which is odd because 'user_email is part of the primary key for 'Rating'.

Well here's what I had to write in order for both foreign keys to stick:

Notice how the second foreign key is fine without an extra 'KEY', but in order to get the 'user_email' foreign key to work, I had to add that 'KEY' after the 'PRIMARY KEY'...

Why is that? How come one would work, but not the other? Why is it that I can't have a table called 'User'

You cannot make references from one table to another
unless there is an index in BOTH tables on the fields
you are referencing. Using 'PRIMARY KEY' and 'KEY'
made indexes for your user_email and article_id
fields so then those fields can be used to reference another
table.
I usually use the 'INDEX' command because I don't
like to add extra key fields.
INDEX user_email_idx (user_email), ...
(this would be added in the CREATE TABLE Rating block.)

The reason why the PRIMARY KEY index didn't work
for field user_email is because user_email is the secondary
key. An index with user_email as the primary key is
required before you can use the field for referential integrity.

I'm not sure, but I think MySQL is confusing your 'USER'
table with the 'USER' table in database 'mysql'.
At the mysql command line, try typing:
use mysql;
show tables;
And you will see that MySQL already has created its own
table called 'USERS'. It's best not to duplicate system
tables, even if they should be in their separate name
space (different databases).

-lv