MySQL: Referential Integrity

I recently wrote an article covering Referential Integrity within Microsoft SQL Server.

This article covers the same subject within MySQL Server. The principle of using “Foreign Keys” is exactly the same.

We will be using phpMyAdmin to add the key(s). This will be familiar to anyone who maintains a MySQL Database. For the purpose of this exercise, I made a “test” database containing a “parent” table and a “child” table (and added a small amount of test data).

MySQL Foreign Keys 006

I would recommend practicing on a test system. If you intend to make changes to a live / production database, ensure that the data and schema are fully backed up!

Using phpMyAdmin, select the Database you wish to work on.

The first stage of the process is to check that BOTH tables in the relationship are using the “InnoDB” storage engine (MyISAM will not work). Within phpMyAdmin, select each table in turn, then set: Operations => Storage Engine = “InnoDB”.

MySQL Foreign Keys 005

If you remember from my previous article, the “Foreign Key” is always added to the “Child” table. So, if you have an “Orders” Table and an “Order Lines” table, the “Foreign Key” should be added to the “Order Lines” Table.

Select the “Child” table and then go to: Structure => Relation View.

MySQL Foreign Keys 007

Our objective is to create a relationship between the “Parent_No” Field in the “Child” and “Parent” tables.

MySQL Foreign Keys 009

Remember, we are working on the “Relation View” of the “Child” table. From here it is a simple process of completing the “Foreign Key” (INNODB) fields for the “Parent_No” field (see screen capture above). I would recommend setting the “On Delete” and “On Update” actions to “Restrict” – this will prevent any parent records from being removed if there are any associated child records.

If you are feeling brave (and have backed up your system), you can test your changes.

I have two “Parent” records in my test system, both of which relate to a number of records in the “Child” table.

MySQL Foreign Keys 003

If I attempt to remove any of the Parent Records, I will now receive an error from the Database Engine (RDMS).

MySQL Foreign Keys 004

That’s basically it! The database will no longer permit the removal of records from the “Parent” table if associated records still exists within the “Child” table.

XAMPP 1.7.3 for Windows: Folder Structure

The following is a summary of the contents of each folder within a Windows XAMPP installation.

Folder Description Version Notes
Anonymous      
Apache Web Server Service 2.2.14 Configuration file is: “apache\conf\httpd.conf”
cgi-bin      
FileZillaFTP FTP Service 0.9.33 Configure using the XAMPP Console.
htdocs Web Site Pages    
install      
licenses      
MercuryMail SMTP Mail Server Service   A simpler alternative is to configure an ISP Mail Server within “php\php.ini”
MySQL MySQL Database Files 5.1.41 Configure using PHPMyAdmin
Perl Perl Scripting Language Folder 5.10.1  
PHP PHP Scripting Language Folder 5.3.1 PHP is enabled by Default. Configure within “php\php.ini”.
PHP\ext “DLL” Files   There are several “dll” files to be found with “php\ext” to support additional functionality (such as SQL Server). These are enabled / disabled within “php.ini”).
PHPMyAdmin MySQL Administration Tool 3.2.4  
Security      
Sendmail “Fake” Sendmail Application   Configure within “sendmail\sendmail.ini”. A simpler alternative is to configure an ISP Mail Server within “php\php.ini”.
Tmp      
WebAlizer Web Server Usage / Analysis Tool    
WebDAV      

 

Note: XAMPP 1.7.7 is the latest version that can be installed on Windows XP / 2003. All newer versions require Windows Vista / 2008 or newer.

Migrate MySQL 4.1 to MySQL 5.x (XAMPP for Windows)

I recently moved a standalone MySQL 4.1.7 Server across to a brand new XAMPP Server Running MySQL 5.1.41. This post sets out the procedure I used.

Backup the Existing Database (on the Source Server)

  1. Load phpMyAdmin
  2. Select the database you wish to Migrate (make a not of the Collation Type)
  3. Select the “Export” Tab
  4. Check “Save as File” Click "Go”

Create an Empty Database (on the new XAMPP Server)

  1. Load phpMyAdmin
  2. Create an Empty Database (with the same name and collation as above)

Import the Database (on the new XAMPP Server)

  1. Within phpMyAdmin, Select the Empty Database (created above)
  2. Select the Import Tab
  3. Browse / Select the “Export File” created previously and Click "Go”
  4. Await confirmation of Successful Import **.

** Failure to create an Empty Database on the New XAMPP Server causes Error 1046 “No Database Selected” during the Import Operation.