The Referencing Action
In the previous section, we deferred the discussion of one part of the foreign key: the referencing action. In that section, we assumed that a player can be deleted only if he or she had not played a match. By defining a referencing action, we can change this "behavior."
Referencing actions can be defined for each foreign key. A referencing action consists of two parts: In the first part, we indicate the statement to which the referencing action applies. Two statements are relevant here: the UPDATE and DELETE statements. In the second part, we specify which action will be taken. There are three possible actions: CASCADE, RESTRICT, and SET NULL. We explain what these different actions mean next.
If you do not specify referencing actions, by default, the following two referencing actions are used:
ON UPDATE RESTRICT ON DELETE RESTRICT
Figure. Create the PENALTIES table with two referencing actions.
Explanation: The first referencing action specifies explicitly that the update must be rejected (RESTRICT) if the number of a player for whom penalties occur in the PENALTIES table is updated (UPDATE). The same applies to the second referencing action: If a player for whom penalties occur in the PENALTIES table is removed (DELETE), the delete must be rejected (RESTRICT).
When CASCADE is used instead of RESTRICT, the behavior changes.
Figure. Create the PENALTIES table with a CASCADE referencing action for the DELETE statement.
DELETE FROM PLAYERS WHERE PLAYERNO = 127
SQL automatically executes the following DELETE statement (behind the scenes):
DELETE FROM PENALTIES WHERE PLAYERNO = 127
If you had specified ON UPDATE CASCADE, the same would have applied to changing the player numbers. If a player number in the PLAYERS table is updated, all player number in the PENALTIES table are updated accordingly.
If you replace the word CASCADE with SET NULL, which is the third possibility, you have another result again:
Figure. Create the PENALTIES table with a SET NULL referencing action for the DELETE statement.
If you delete a player, the player number is replaced by the NULL value in all rows of the PENALTIES table in which that player number appears.
The previous statement is not actually correct. This is because the PLAYERNO column in the PENALTIES table has been defined as NOT NULL, which means that no NULL values can be entered. Therefore, SQL will not accept the previous CREATE TABLE statement.
Not every SQL product supports referencing actions.