Referential Integrity with MySQL

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');

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;

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.

Mike Maguire