Foreign Key in SQL

In the lesson about joins, we matched rows from the clients table with rows from the salespersons table.

The clients table contained a column called salesperson_id. We matched this column to the column called id in the salespersons table:

In this example :

  • salesperson_id is the foreign key (key having the value of a column in another table).
  • id of salespersons is the parent key.
  • we call clients the child table or the referencing table.
  • we call salesperson the parent table or the referenced table.
  • A salesperson can take care of one or more clients. Example: the sales person which id equals 1 takes care of clients Aadit Gupta and Aamir Malhotra.
  • There cannot be more than one salesperson taking care of one client (in the column salesperson_id, there cannot be more than one value in one field).

Syntax:

ALTER TABLE clients
ADD FOREIGN KEY (salesperson_id) REFERENCES salespersons(id);

In this statement:

  • clients is the child table. This table contains the column that we use as a foreign key.
  • salesperson_id is the column that we use as a foreign key.
  • id is the column we use as a parent key. It helps performance if this column is also the primary key of the table salespersons.
  • salespersons is the table containing the column that we use as a parent key.

Foreign key is a constraint.

Having a foreign key prevents the following actions:

  • inserting a row in the child table with a value in the foreign key not corresponding to any value in the parent key.
  • deleting a row in the parent table matching at least one row in the child table.

Example:

Cascading on update or a delete to the child table.

You can add a rule that if a parent row is deleted, all corresponding child rows are deleted as well:
To add such a rule, add “ON DELETE CASCADE” at the end of the statement creating the foreign key.

Example:

On table salespersons: if I delete a row in salespersons, then the rows having salespersons_id equal to this salesperson’s id will be deleted.


If you want to test your understanding of this article, take the quiz on foreign keys. You can also learn read about the latest techniques on graspsql’s blog.
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: November 6, 2014