Foreign Keys

• http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

Foreign Keys provide a means to maintain “referential integrity.” Since primary keys are often stored in related tables instead of duplicating unnecessary data, we must make sure that the record referenced by a primary key exists.

If we have a Customers table, and a Sales table, we will keep a reference to each customer in each Sales table record by way of the customer_id primary key. If we were to allow someone to delete a Customer record, we would have no way to find out the Customer name and other details when we analyze the Sales table.

Foreign keys allow us to establish a relationship between columns of different tables, to make sure we always have what we need. With foreign keys in place, we would prevent the above problem from happening. In order to delete a Customer, all records in the Sales table would need removed first, and then the DBMS would allow the Customer to be removed.

drop table if exists Sales;
drop table if exists Customers;
drop table if exists Products;

create table Customers(
	customer_id int(11) not null auto_increment,
	name varchar(50) not null,
	PRIMARY KEY (customer_id)
)ENGINE=INNODB;

create table Products(
	product_id int(11) not null auto_increment,
	name varchar(50) not null,
	price double not null default 0.00,
	PRIMARY KEY (product_id)
)ENGINE=INNODB;

create table Sales(
	sale_id int(11) not null auto_increment,
	product_id int(11) not null,
	customer_id int(11) not null,
	price double not null,
	PRIMARY KEY (sale_id),
	FOREIGN KEY (product_id) REFERENCES Products(product_id),
	FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
)ENGINE=INNODB;

INSERT INTO Customers (name) VALUES ("Widget, Inc."), ("Acme, Inc."), ("Thing-a-majig Corp.");

INSERT INTO Products (name, price) VALUES
("Printer", 299.99),
("Monitor", 149.99),
("Keyboard", 19.99),
("Mouse", 19.99),
("Computer", 699.99);

INSERT INTO Sales (product_id, customer_id, price) VALUES
(5,2,699.99),
(1,1,299.99),
(2,1,145.99),
(3,3,19.99),
(4,3,19.99);

/*
The following would fail, because Widget, Inc. has purchase
records listed in the Sales table.  Note that MySQL must use
INNODB as the table ENGINE for Foreign Key Constraints to
work.  MYSAM will not honor Foreign Key's, and will not
report an error on table creation.

delete from customers
where name = "Widget, Inc."
*/