Foreign Keys

1
Foreign Keys
2
Foreign Keys

• Topics
• FK Definition
• 5 Referential Actions
• Advantages and Disadvantages
• Script

3
Foreign Keys

• Definition
• A foreign key is a referential constraint
• A foreign key is an attribute in a child table
  that refers to a primary key in a parent table

4
Foreign Keys
5
Foreign Keys

• Referential Integrity
• Cant update or insert a row into a child or
  referencing table (Product) that doesnt match a
  row in the parent or referenced table
  (Manufacturer)
• What happens to the values on the child table
  upon change in the parent table is determined by
  the Referential Actions
• CASCADESET NULL
• RESTRICT
• NO ACTION
• SET DEFAULT

6
Foreign Keys
Referential Actions- What happens to child table
as parent table changes (update/delete) CASCADE
Delete or update the row from the parent table
and automatically delete or update the matching
rows in the child table. SET NULL Delete or
update the row from the parent table and set to
null matching rows in child table (Note FK cant
have the NOT NULL qualifier specified) These two
are the same as having no FK constraint RESTRICT
Rejects the delete or update NO ACTION
(Restrict and No Action are the same) SET
DEFAULT () FK values are set to the column
default. (Note Not supported by InnoDB)
7
Foreign Keys

• Advantages and Disadvantages
• Help avoid inconsistency in database
• Centralized checking of constraints by the
  database server
• Cascading updates and deletes can simplify the
  application code
• Properly designed foreign key rules aid in
  documenting relationships between tables
• With FK constraints its easier to recover from
  an error that happened in the middle of
  multiple-record inserts (versus having your code
  handle this)

8
Foreign Keys

• Advantages and Disadvantages
• Additional overhead for the database server to
  perform the necessary checks (probably only
  relevant with bigger DB systems)
• Note If problems arise when restoring DB from
  mysqldump, temporarily disable FK checks

9
Foreign Keys

• MySQL Notes
• Foreign keys cant be used in Temp Tables
• Tables must be InnoDB
• Foreign key must be of same data type as Primary
  Key its referencing
• Indexes are created automatically for all FK
  fields

10
Foreign Keys
Referential ActionsCASCADE SET NULL RESTRICT
NO ACTION SET DEFAULT
CREATE TABLE manufacturer (mid INT NOT NULL,
mname tinytext,mphone varchar(15),PRIMARY KEY
(mid) ) ENGINEINNODB CREATE TABLE product
(pid INT NOT NULL, pname tinytext, mid INT NOT
NULL,FOREIGN KEY (mid) REFERENCES
manufacturer(mid) ON UPDATE CASCADE ON DELETE
RESTRICT) ENGINEINNODB
11
Foreign Keys
Referential ActionsCASCADE SET NULL RESTRICT
NO ACTION SET DEFAULT

• Lets see what happens when we
• Delete from Child table, with FK constraint
  middelete from product where pid1
• Insert into Child table, with invalid midinsert
  into product (pid,pname,mid) values (2,Beer
  Cups,36)
• Update Parent Table, with Cascading
  Updatesupdate manufacturer set mid30 where
  mid1
• Delete from Parent Table, with Delete
  Restrictdelete from manufacturer where mid1

12
Foreign Keys