how do I add a foreign key pointing to the same table using phpMyAdmin?

Question

I have an existing InnoDB table which already has foreign keys pointing to different tables.

But when I try to create a foreign key pointing to the Primary index, I get an error (check data type).

The table is User with User_Id as the Primary.

I want a foreign key Manager_ID which is a FK to User_Id.

Both of INT Both of Length 10 Unsigned...

But I still get a data check error...?

Are you talking about giving a table a foreign key *to itself*? — chaos, Mar 23 '09 at 20:14
That is what it seems. But maybe we are mistaken since he also talks about an InnoDB table. I think we may need more information about the database schema. My answer may or may not be what he is looking for. — Kevin Crowell, Mar 23 '09 at 20:19
Nothing wrong with having a foreign key to the primary key of the same table is there, e.g. usertable has FK link between loan officer and loan supervisor. — James Piggot, Mar 23 '09 at 20:35
That "length of 10" has nothing to do with the size of the integer. This is a common misconception about MySQL. INT(10) only means it pads to 10 digits if you *also* define the column as ZEROFILL. — Bill Karwin, Mar 23 '09 at 23:29

score 14 · Accepted Answer · edited Nov 29 '13 at 18:02

14

Make sure that Manager_ID is not set to NOT NULL.

You have to allow nulls on that field, as the top-most person in the company will have no manager.

~~I found a post over on the MySQL boards that might help.~~

edited Nov 29 '13 at 18:02

Andrew Barber

answered Mar 23 '09 at 20:24

Powerlord

1 Answers1