Setting up foreign keys in phpMyAdmin?

294

I'm setting up a database using phpMyAdmin. I have two tables (foo and bar), indexed on their primary keys. I am trying to create a relational table (foo_bar) between them, using their primary keys as foreign keys.

I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn't support foreign keys. All id fields are INT(11).

When I choose the foo_bar table, click the "relation view" link, and try to set the FK columns to be database.foo.id and database.bar.id, it says "No index defined!" beside each column.

What am I missing?

Clarification/Update

For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin.

Also, although I haven't been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this:

SELECT * 
FROM foo 
INNER JOIN foo_bar 
ON foo.id = foo_bar.foo_id 
INNER JOIN bar
ON foo_bar.bar_id = bar.id;

It just makes me uncomfortable not to have the FKs explicitly defined in the database.

Nathan Long

출처 mysql phpmyadmin

10 답변

324

If you want to use phpMyAdmin to set up relations, you have to do 2 things. First of all, you have to define an index on the foreign key column in the referring table (so foo_bar.foo_id, in your case). Then, go to relation view (in the referring table) and select the referred column (so in your case foo.id) and the on update and on delete actions.

I think foreign keys are useful if you have multiple tables linked to one another, in particular, your delete scripts will become very short if you set the referencing options correctly.

EDIT: Make sure both of the tables have the InnoDB engine selected.

Tip: Relations view is little link under your table, it was hard for me to find it in the first place - Mladen Janjetovic
Unless that link doesn't show there, in which case: Make sure your table is of type InnoDB (under the Operations tab in phpMyAdmin) for this to not happen. - muttley91
@muttley91My table is InnoDB. I double-checked. The link still doesn't show up. - afilina
It's in there - imgur.com/gw80CWC - shershen
@afilina In the new version of phpMyAdmin, it is visible at the top inside "Structure" tab, just below the tab row that shows "Browse", "Structure", etc. - Astitva Srivastava

210

phpMyAdmin lets you define foreign keys using their "relations" view. But since, MySQL only supports foreign constraints on "INNO DB" tables, the first step is to make sure the tables you are using are of that type.

To setup a foreign key so that the PID column in a table named CHILD references the ID column in a table named PARENT, you can do the following:

For both tables, go to the operations tab and change their type to "INNO DB"
Make sure ID is the primary key (or at least an indexed column) of the PARENT table.
In the CHILD table, define an index for the PID column.
While viewing the structure tab of the CHILD table, click the "relation view" link just above the "add fields" section.
You will be given a table where each row corresponds to an indexed column in your CLIENT table. The first dropdown in each row lets you choose which TABLE->COLUMN the indexed column references. In the row for PID, choose PARENT->ID from the dropdown and click GO.

By doing an export on the CHILD table, you should see a foreign key constraint has been created for the PID column.

Wow, very important thing to know. This page was not the first thing I found when looking for help with adding a foreign key, I wish this were mentioned more often. - user1299656

This is a summary of a Wikipedia article. It specifies the different types of relationships you can stipulate in PHPmyadmin. I am putting it here because it is relevant to @Nathan's comment on setting the foreign keys options for "on update/delete" but is too large for a comment - hope it helps.

CASCADE

Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[2]).

RESTRICT

A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.

NO ACTION

NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.

SET NULL

The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.

SET DEFAULT

Similar to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.

Better yet, go directly to the MySQL source documentation: dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html - kmoser

In phpmyadmin, you can assign Foreign key simply by its GUI. Click on the table and go to Structure tab. find the Relation View on just bellow of table (shown in below image).

You can assign the forging key from the list box near by the primary key.(See image below). and save

corresponding SQL query automatically generated and executed.

For those new to database .... and need to ALTER an existing table. A lot things seem to be pretty straightforward, but there is always something ... between A and B.

Before anything else, take a look at this.

Make sure you have P_ID (parent ID on both parent and child table).
Of course it will be already filled in the parent. Not necessarily in the child in a true and final way. So for instance P_ID #3 (maybe many times in the child table will be pointing to original P_ID at parent table).
Go to SQL tab (I am using phpMyAdmin, should be similar in other ones) and do this command:

ALTER TABLE child_table_name
ADD FOREIGN KEY (P_ID)
REFERENCES parent_table_name (P_ID)
Click on child table, than structure, finally on relational view. Finish your DB planning there. There was a nice answer before this one about cascade, restrict, etc. Of course it could be done by commands...

Foreign key means a non prime attribute of a table referes the prime attribute of another *in phpMyAdmin* first set the column you want to set foreign key as an index

then click on RELATION VIEW

there u can find the options to set foreign key

InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

On the other hand, if MyISAM has advantages over InnoDB in your context, why would you want to create foreign key constraints at all. You can handle this on the model level of your application. Just make sure the columns which you want to use as foreign keys are indexed!

Foreign key constraints save me a lot of effort and potential errors. For example, let's say I'm going to delete a user from my system. I could write code that specifies every place in the database where data about that user exists, and tell it to delete it. But I have to keep that delete function up-to-date at all times. On the other hand, if all user-related data has has a FK to the user's ID and is set to cascade on delete, all my code has to say is "delete this user" and the database will take care of deleting everything that has a FK reference to that user. Much cleaner to maintain. - Nathan Long
@Nathan: That's what I'm saying in the post. You can handle that on the model level too. You can implement on delete cascade from the model. - markus
You almost always add referential integrity to your database tables. The database should protect itself from bad application programming. Do not rely on your application alone to maintain data integrity. Of course there are always exceptions to every rule, but I have not found one for this. - Harv

Don't forget that the two columns should have the same data type.

for example if one column is of type INT and the other is of type tinyint you'll get the following error:

Error creating foreign key on [PID column] (check data types)

Step 1: You have to add the line: default-storage-engine = InnoDB under the [mysqld] section of your mysql config file (my.cnf or my.ini depending on your OS) and restart the mysqld service.

Step 2: Now when you create the table you will see the type of table is: InnoDB

Step 3: Create both Parent and Child table. Now open the Child table and select the column U like to have the Foreign Key: Select the Index Key from Action Label as shown below.