Setting up foreign keys in phpMyAdmin?

user_4376 0
Viewed 689692

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.

phpmyadmin mysql
14 Answers

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

user_30310240

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!

user_119950

This is old thread but answer because if useful to anyone.

Step 1. Your Db Storage Engine set to InnoDB

Step 2. Create Primary Table

here customer is primary table and customer_id is primary key

enter image description here

Step 3. create foreign key table and give index

here we have customer_addresses as related table and store customer addresses, so here customer_id relation with customer table

we can select index directly when create table as below

enter image description here

If you forgot to give index when create a table, then you can give index from the structure tab of table as below.

enter image description here

Step 4. Once index give to the field, Go to structure tab and click on Relation View as shown in below pic

enter image description here

Step 5. Now select the ON DELETE and ON UPDATE what you want to do, Select column from current table, select DB (SAME DB), select relation table and primary key from that table as shown in below pic and Save it

enter image description here

Now check if relation are give successfully, go to foreign table data list and click on foreign key value, you will redirect to primary table record, then relation made successfully.

user_63094570

Make sure you have selected your mysql storage engine as Innodb and not MYISAM as Innodb storage engine supports foreign keys in Mysql.

Steps to create foreign keys in phpmyadmin:

  1. Tap on structure for the table which will have the foreign key.
  2. Create INDEX for the column you want to use as foreign key.
  3. Tap on Relation view, placed below the table structure

relation view

  1. In the Relation view page, you can see select options in front of the field (which was made an INDEX).

setting foreign key relation

UPDATE CASCADE specifies that the column will be updated when the referenced column is updated,

DELETE CASCADE specified rows will be deleted when the referenced rows are deleted.

Alternatively, you can also trigger sql query for the same

ALTER TABLE table_name
ADD CONSTRAINT fk_foreign_key_name
FOREIGN KEY (foreign_key_name)
REFERENCES target_table(target_key_name);
user_41981800

First set Storage Engine as InnoDB

First set Storage Engine as InnoDB

then the relation view option enable in structure menu

then the relation view option enable

user_75405730

Newer versions of phpMyAdmin don't have the "Relation View" option anymore, in which case you'll have to execute a statement to achieve the same thing. For example

ALTER TABLE employees
    ADD CONSTRAINT fk_companyid FOREIGN KEY (companyid)
    REFERENCES companies (id)
    ON DELETE CASCADE;

In this example, if a row from companies is deleted, all employees with that companyid are also deleted.

user_13804790
user_42420860
Related