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

mysql foreign-keys innodb self-join phpmyadmin
AFG picture AFG · Mar 23, 2009 · Viewed 9.5k times · Source

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...?

Answer

Powerlord picture Powerlord · Mar 23, 2009

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.

Related questions

How do I see all foreign keys to a table or column?

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL.

Read More
MySQL DROP all tables, ignoring foreign keys

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?

Read More
MySQL foreign key constraints, cascade delete

I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB). How do I make a SQL statment that DELETE ON CASCADE? If I delete a category then how do I make sure that …

Read More