Foreign Keys

tags: #sql/constraints

Specifying foreign key constraint

A foreign key is a column or a set of columns in a table that refers to the primary key of another table.

It is used to establish a relationship between two tables and enforce referential integrity between them (i.e., the values in the foreign key column(s) must match the values in the primary key column(s) of the referenced table or be NULL).

To establish foreign keys, we can use the FOREIGN KEY clause:

CREATE TABLE table_name (
	column1 datatype constraint(s),
	fk_column datatype constraint(s),
	...
	FOREIGN KEY (fk_column) REFERENCES referenced_table(pk_column)
)

Adding foreign key constraint

To add a FK to an existing table, we can use the ALTER TABLE statement:

ALTER TABLE referencing_table
ADD FOREIGN KEY (fk_column) REFERENCES referenced_table(pk_column);

Adding referential triggered action clause

What is a referential triggered action?

A referential triggered action clause specifies what action should be taken on related rows in a table when a corresponding row in another table is updated or deleted. There are two types of referential triggered actions:

1. ON UPDATE
2. ON DELETE

General syntax for using a referential triggered action clause in MySQL:

CREATE TABLE table_name (
    column1 datatype contraint(s),
    column2 datatype constraint(s),
    ...
    FOREIGN KEY (column_name)
    REFERENCES referenced_table (column_name)
    ON UPDATE <action>
    ON DELETE <action>
);

The <action> parameter specifies the action to be taken on the related rows in the parent table when a corresponding row in the child table is updated or deleted.

What type of actions can be done?

• CASCADE: When a row in the parent table is updated or deleted, all related rows in the child table are also updated or deleted.
  
  
• SET NULL: When a row in the parent table is updated or deleted, the corresponding foreign key column in the child table is set to NULL.
  
  
• RESTRICT: Prevents the update or deletion of a row in the parent table if there are related rows in the child table.
  
  
• NO ACTION: This is the default option, and it has the same effect as RESTRICT.
  
  
• SET DEFAULT: when a row is deleted from the referenced table, the foreign key value in the referencing table will be set to the default value specified for the column.