Referential Integrity with MySQL

You should not use this code on a production website.

Warning: This tutorial uses old techniques. It is insecure and will leave your server vulnerable to SQL Injection attacks.This tutorials also uses mysql_ functions that are no longer support. For updated tutorials look for a PDO or MySQLi tutorial.This post will be delete or revised in the future.

Referential Integrity is when the rows from one table are “cross-referenced” and verified with those in another table. This tutorial will walk you through using MySQL to enforce this rule. For example, let’s say you want to have a customer table that contains all the information about your customers and then you want to have an invoice table that will keep the information for invoices. This invoice table will need to be associated to the customer table through the customer id. This relationship would allow you to get all invoices for a customer by searching for their ID. Enforcing referential integrity also will prevent the insertion of a invoice with a customer ID that isn’t in the customer table. Let’s get started.

First, we need to decide what our tables are going to look like. These could vary based on the application you are working with but for this tutorial, we will keep them basic. First, the customer table which doesn’t need to be anything special except ensuring that the type is INNODB and that you set the primary key.

CREATE TABLE customers 
(
   customer_id INT NOT NULL AUTO_INCREMENT,
   name 			VARCHAR(50),
	address  	VARCHAR(60),
	city 			VARCHAR(45),
	state			VARCHAR(2),
	zip 			INT,
   PRIMARY KEY (customer_id)
) TYPE = INNODB;

This statement will create a table called customers that contains customer_id, name, address, city, state, and zip columns. It sets the customer id field to the primary key and makes it auto increment for each insert. Next we need to build the invoice table that will house the information about an invoice. (*Note: Normally an invoice would reference an item table to house the items for each invoice, but for sake of remaining simple, we will just have an items field that contains a varchar with comma separated items)

CREATE TABLE invoice 
(
   invoice_id		INT NOT NULL AUTO_INCREMENT,
   customer_id	   INT,
   submit_date    TIMESTAMP NOT NULL,    
	total				DECIMAL(10,2),
	tax				DECIMAL(10,2),
	shipping			DECIMAL(10,2),
	items				VARCHAR(100),
   PRIMARY KEY(invoice_id),
   INDEX (customer_id),
   FOREIGN KEY (customer_id) REFERENCES customers (customer_id) 
) TYPE = INNODB;

This statement will create the invoice table containing invoice id, customer id, submit_date, total, tax, shipping, and items, columns. It sets the primary key to the invoice_id. INDEX tells it to index that column so that when we search the DB for that value it will be faster to return the results. Then the foreign key sets the customer_id of this table to look at the customer_id of the customer table to make sure it exists.

Let’s go ahead and make sure everything works as it is supposed to. First, we will try to insert an invoice for a customer ID that we haven’t inserted yet. Use the following insert statement.

 insert into invoice (customer_id,submit_date,total,tax,shipping,items) values (1,current_timestamp,54.22,4.50,10.25,'RAM, Power Supply, Case');

referential_integrity_with_mysql_01

You should receive an error similar to the above picture. This just means that there is no customer with that ID so you can’t insert an invoice for them. Next, lets add the customer to the customer table.

insert into customers (name,address,city,state,zip) values ('Mike','100 That One Place','Anywhere','TX',22222);

Once you insert that row you should be able to successfully insert the invoice without any issues. The final thing that will referential integrity will enforce is to ensure you can’t delete customers that have invoice attached to them. Let’s try to ensure it works.

delete from customers where customer_id = 1;

referential_integrity_with_mysql_02

You should receive the error above. If you delete the invoice from the invoice table first, you will then be able to delete the customer. That concludes this intro to referential integrity. I hope this is clear to you and thanks for reading.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join the TeamTutorials mailing list and get the latest tips, tricks, and special discounts for members only.

Thanks! Please click the link in the email we just sent you to confirm.

  • In the first example your insert query inserts into name then value, yet the name field is empty and the value has the name in it. are you sure you havent mixed up your queries?

    as for the second part, you’re inserting into a table that has a key setup, but you don’t have vals in the first table. MySQL should be reporting an error i’d say if you can add to the second table without a PK in the first… i’m gonna go do some MySQL testing.

  • The queries are correct, I don’t see any issues with them.

    Yes, It does throw an error as the picture is shown above. That was the point, was to show that you could not insert a value without it existing in the first table.

    Thanks for reading.

  • First, if the ‘database’ you are referring to is a legacy data system from the days before relational databases (I’m working with one now in a project and it is sooo frustrating, then you clearly can not have referential integrity.

  • One table has the customer where id is key, along with billing address
    Another table has orders which contains the customer key in the row but no billing information.

  • it is important that the underlying engine act in ways that preserve the integrity and validity of the data. This means the RDBMS should stand guard and deny entry of any and all invalid data through the welcome gates of the actual database.

  • Integrity is action based on a consistent framework of principles. Depth of principles and adherence of each level to the next are key determining factors. One is said to have integrity to the extent that everything he does and believes is based on the same core set of values.

  • You should be able to use mysql as your database with .net. I don’t see any problems with that. You would not however will be able to install .net framework on a linux machine. There are variations available e.g.

  • I know there are php tutorial web sites and what not but what is the most efficient? I can not spend any money and am needing something that is to the point.

  • @web design brisbane

    I believe there are project working to allow asp.net to run on apache/linux servers.

    @football
    PHP/MySQL is the way to go right now in my opinion. You said you can spend money. You can setup a local WAMP server for free wampserver.com to test your PHP/MySQL projects. If you are interest in ASP.NET then download Microsoft Visual Web Developer (for free also).

    One thing to note is that Microsoft is even taking notice to PHP. IIS can now support PHP. The only upside I see to .NET is that it is compiled. If you were to sell a project .NET would be more secure. Thought I am sure it can be decompiled and PHP can be obfuscated if you are worried about code theft.

  • Today I’ve been doing some tests using MySQL together with the JDeveloper 11 Preview Release. This worked fine, most of the info is covered in the Help chapter, “Working with Non-Oracle Database”.

  • Probably because they weren’t formally trained and learned from somebody else who didn’t get into this practice. They won’t consider that until they start having problems with their work..

  • I agree fotboll.Thanks.

  • Thank you for the great info, I have been running around like a headless chicken trying to figure this out for a loong time.

  • MySQL is the world’s most popular open source relational database management system. It is widely used web applications due to fast performance, high reliability and scaliblity, cost-effectiveness. Several powerful and smart features make MySQL the ultimate choice for web applications.

  • You showing nice codes its very helpful for the beginner. I really like the MySql all my system MySql database. I really appreciate the power.