phpMyAdmin Foreign Keys

This guide is to setup phpMyAdmin to support Foreign Keys for your MySQL database.

Task: What I want to do is, when I delete or update a teacher in the teachers table, it will cascade changes down to students in the students table. Or when I delete a school from the schools table, it will remove/update any associated teachers and students that are related to that school being deleted/updated.

Hierarchy of Tables and Keys

The example in this post uses 3 tables:

- schools
--- teachers
------ students

Primary keys:

schools.schoolid
teachers.teacherid
students.studentid

With these wanna-be foreign keys, I have students that will to be referenced to their teacher, and their teacher to be referenced to a school:

teachers.schoolid
students.teacherid

The trick in phpMyAdmin is to work from the bottom up. We will start with the students table to link up to the teachers table - remember the hierarchy of tables (above). Check the bottom of this post to get a copy of the SQL used in this tutorial. Note: You might want to create a new database and run this SQL.

Setup a InnDo

First setup your tables so that the storage engine is set to: InnDo. This will allow you to have foreign keys. I will assume you have created a new database and successfully run the SQL above.

  • With the list of tables in the left frame, select the first table, schools
  • Click the 'Operations' tab
  • Change the 'Storage Engine' to InnDo
  • Click the 'Go' button in that section to save changes
  • Repeat these steps for the students and teachers tables

Setup Foreign keys

Here are the steps in order:

  • With the list of tables in the left frame, click the students table. This is the lowest table in the hierarchy of table above
  • Click the 'Structure' tab
  • In our example, we want the foreign key for the teachers table to be students.teacherid. This will be how we link the two tables together for cascading deletes/updates. Click the 'Index' button on the row of students.teacherid. This is the image with a yellow lightening sign on it.
  • Now click 'Relation View' underneath the field listing
  • Now we can set the Foreign Keys. We want the teacherid to link to teachers.teacherid
  • Then we can change the 'ON DELETE' drop down to 'CASCADE' and select the same option of 'ON UPDATE' if required.
  • Click 'Save'

- You can repeat these steps for the teachers table. The only difference being:

  • You need to make the index on teachers.schoolid
  • You want to set the Foreign Keys to link to schoolid to link to schools.schoolid

Check that it worked

You can check that cascading works by viewing the before and after results in the students and teachers tables. Example, take not of a teacher ID in teachers table and delete it. Check the students table to see if there are any records left referencing the teacher ID you just deleted. If there isn't, you have successfully configured foreign keys in phpMyAdmin.

You can copy and paste this into a new database in phpMyAdmin and it will create and populate tables used in this example:

-- Table structure for table `schools`

CREATE TABLE IF NOT EXISTS `schools` (
`schoolid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`schoolid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `schools` (`schoolid`, `name`) VALUES
(1, 'Flemming High School'),
(2, 'Norsa Primary School');

-- Table structure for table `students`

CREATE TABLE IF NOT EXISTS `students` (
`studentid` int(11) NOT NULL AUTO_INCREMENT,
`teacherid` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`studentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
INSERT INTO `students` (`studentid`, `teacherid`, `name`) VALUES
(1, 2, 'Sheard Melchior'),
(2, 1, 'Sora Marsha'),
(3, 2, 'Cody Nathanael'),
(4, 3, 'Hilaria Gyorgyike'),
(5, 1, 'Jakob Emmanouil'),
(6, 4, 'Sefton Jaqueline'),
(7, 2, 'Gussie Milo');

-- Table structure for table `teachers`

CREATE TABLE IF NOT EXISTS `teachers` (
`teacherid` int(11) NOT NULL AUTO_INCREMENT,
`schoolid` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`teacherid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `teachers` (`teacherid`, `schoolid`, `name`) VALUES
(1, 1, 'Ms Jane Smith'),
(2, 1, 'Mr Whiffle Junior'),
(3, 2, 'Miss Alice Stelling'),
(4, 2, 'Mr John Pierce');

-- Table structure for table `schools`

CREATE TABLE IF NOT EXISTS `schools` (
`schoolid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`schoolid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `schools` (`schoolid`, `name`) VALUES
(1, 'Flemming High School'),
(2, 'Norsa Primary School');

-- Table structure for table `students`

CREATE TABLE IF NOT EXISTS `students` (
`studentid` int(11) NOT NULL AUTO_INCREMENT,
`teacherid` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`studentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
INSERT INTO `students` (`studentid`, `teacherid`, `name`) VALUES
(1, 2, 'Sheard Melchior'),
(2, 1, 'Sora Marsha'),
(3, 2, 'Cody Nathanael'),
(4, 3, 'Hilaria Gyorgyike'),
(5, 1, 'Jakob Emmanouil'),
(6, 4, 'Sefton Jaqueline'),
(7, 2, 'Gussie Milo');

-- Table structure for table `teachers`

CREATE TABLE IF NOT EXISTS `teachers` (
`teacherid` int(11) NOT NULL AUTO_INCREMENT,
`schoolid` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`teacherid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `teachers` (`teacherid`, `schoolid`, `name`) VALUES
(1, 1, 'Ms Jane Smith'),
(2, 1, 'Mr Whiffle Junior'),
(3, 2, 'Miss Alice Stelling'),
(4, 2, 'Mr John Pierce');


Top
Back to Blogs