Setting up foreign keys in phpMyAdmin

Question

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  quot relation view quot  link  and try to set the FK columns to be database foo id and database bar id  it says  quot No index defined  quot  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

User · Answer

From the official MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.

User · Answer

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 · Answer

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

User · Answer

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 · Answer

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

User · Answer

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     Step 4  Now open the Relation View in the same child table from bottom near the Print View as shown below    Step 5  Select the column U like to have the Foreign key as Select the Parent column from the drop down  dbName TableName ColumnName  Select appropriate Values for ON DELETE and ON UPDATE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

First set Storage Engine as InnoDB    then the relation view option enable in structure menu

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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    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    If you forgot to give index when create a table  then you can give index from the structure tab of table as below     Step 4  Once index give to the field  Go to structure tab and click on Relation View as shown in below pic    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    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 · Answer

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     Step 4  Now open the Relation View in the same child table from bottom near the Print View as shown below    Step 5  Select the column U like to have the Foreign key as Select the Parent column from the drop down  dbName TableName ColumnName  Select appropriate Values for ON DELETE and ON UPDATE

User · Answer

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 · Answer

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 · Answer

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

User · Answer

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    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    If you forgot to give index when create a table  then you can give index from the structure tab of table as below     Step 4  Once index give to the field  Go to structure tab and click on Relation View as shown in below pic    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    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 · Answer

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 · Answer

First set Storage Engine as InnoDB    then the relation view option enable in structure menu

User · Answer

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 · Answer

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   Tap on structure for the table which will have the foreign key  Create INDEX for the column you want to use as foreign key  Tap on Relation view  placed below the table structure    In the Relation view page  you can see select options in front of the field  which was made an INDEX     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 · Answer

From the official MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.

User · Answer

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   Tap on structure for the table which will have the foreign key  Create INDEX for the column you want to use as foreign key  Tap on Relation view  placed below the table structure    In the Relation view page  you can see select options in front of the field  which was made an INDEX     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 · Answer

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

[mysql] Setting up foreign keys in phpMyAdmin?

Examples related to mysql

Examples related to phpmyadmin