Some definitions of foreign key:-
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
A foreign key is a field in a relational table that matches the primary key of another table. The foreign key can be used to cross-reference tables. A table may have multiple foreign keys, and each foreign key can have a different referenced table.
A Foreign Key is a Referential Constraint between two tables. A Referential Constraint or Referential Integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).
For MySQL, to support foreign key, the table type should be ‘InnoDB‘. Therefore, when we are cross-referencing two tables through foreign key, both the tables must be InnoDB tables and they must not be Temporary tables.
InnoDB also supports foreign key references within a table. The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known as a self-referencing or recursive foreign key.
When the user attempts to delete or update a row from a parent table, and there are one or more matching rows in the child table, InnoDB handles this through “Referential Action“. There are five options regarding the action to be taken. If ON DELETE or ON UPDATE are not specified, the default action is RESTRICT.
CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.
SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.
NO ACTION: In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary key value is not permitted to proceed if there is a related foreign key value in the referenced table. InnoDB rejects the delete or update operation for the parent table.
RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. However, in MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.
SET DEFAULT: Similar to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted. This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.
Here, I will be implementing the foreign key CASCADE update and delete.
CASCADE:-
Whenever rows in the master (referenced) table are deleted, the respective rows of the child (referencing) table with a matching foreign key column will get deleted as well. This is called a cascade delete.
Example Tables: test_customer(customer_id, name, address) and test_order(order_id, customer_id, status)
test_customer is the master table and test_order is the child table, where ‘customer_id’ is the foreign key in test_order and represents the customer who placed the order. When a row of test_customer is deleted, any test_order row matching the deleted test_customer’s customer_id will also be deleted.
Create Tables
CREATE TABLE test_customer (
customer_id int(10) unsigned NOT NULL auto_increment,
name varchar(100),
address varchar(100),
PRIMARY KEY (customer_id)
) ENGINE=InnoDB;
CREATE TABLE test_order (
order_id int(10) unsigned NOT NULL auto_increment,
customer_id int(10) unsigned NOT NULL,
status varchar(30),
PRIMARY KEY (order_id)
) ENGINE=InnoDB;
Add Foreign Key
ALTER TABLE test_order
ADD CONSTRAINT FK_test_order
FOREIGN KEY (customer_id) REFERENCES test_customer(customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE;
Alternative way
Alternatively we can directly add Foreign Key at the time of table creation. You may choose any one way (the following way or the way above) for creating tables and assigning foreign key.
SET foreign_key_checks = 0;
CREATE TABLE test_customer (
customer_id int(10) unsigned NOT NULL auto_increment,
name varchar(100),
address varchar(100),
PRIMARY KEY (customer_id)
) ENGINE=InnoDB;
CREATE TABLE test_order (
order_id int(10) unsigned NOT NULL auto_increment,
customer_id int(10) unsigned NOT NULL,
status varchar(30),
PRIMARY KEY (order_id),
CONSTRAINT FK_test_order
FOREIGN KEY (customer_id) REFERENCES test_customer(customer_id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
SET foreign_key_checks = 1;
Insert sample data to test_customer and test_order tables
INSERT INTO test_customer
( customer_id , name , address )
VALUES
( '1', 'John', 'New York' ),
( '2', 'Tom', 'London' );
INSERT INTO test_order
( order_id , customer_id , status )
VALUES
( '1', '1', 'pending' ),
( '2', '1', 'processing' ),
( '3', '2', 'complete' ),
( '4', '2', 'pending' );
Doing UPDATE CASCADE
When we update customer_id in test_customer (parent table), the customer_id field in test_order (child table) will also get updated.
UPDATE test_customer SET customer_id = 3 WHERE customer_id = 1;
Note:-
We CANNOT directly update the foreign key in child table.
Doing this will result in error saying “Cannot add or update a child row: a foreign key constraint fails“.
UPDATE test_order SET customer_id = 4 WHERE customer_id = 3;
Doing DELETE CASCADE
When we delete customer_id in test_customer (parent table), the customer_id field in test_order (child table) will also get deleted.
DELETE FROM test_customer WHERE customer_id = 2;
Note:-
We CAN directly delete rows from test_order (child table) like below:-
DELETE FROM test_order WHERE customer_id = 3;
Hope this helps. Thanks
References:-
1. http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
2. http://en.wikipedia.org/wiki/Foreign_key