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).
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.
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.
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.