The Referencing Action






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.

CREATE   TABLE PENALTIES
        (PAYMENTNO     INTEGER NOT NULL,
         PLAYERNO      INTEGER NOT NULL,
         PAYMENT_DATE  DATE NOT NULL,
         AMOUNT        DECIMAL(7,2) NOT NULL,
         PRIMARY KEY  (PAYMENTNO),
         FOREIGN KEY  (PLAYERNO) REFERENCES PLAYERS (PLAYERNO)
            ON UPDATE  RESTRICT
            ON DELETE  RESTRICT)

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.

CREATE   TABLE PENALTIES
        (PAYMENTNO     INTEGER NOT NULL,
         PLAYERNO      INTEGER NOT NULL,
         PAYMENT_DATE  DATE NOT NULL,
         AMOUNT        DECIMAL(7,2) NOT NULL,
         PRIMARY KEY (PAYMENTNO),
         FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO)
            ON DELETE CASCADE)

Explanation If a player is deleted, all his or her penalties are automatically removed as well. Imagine that the following DELETE statement is executed:

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.

CREATE   TABLE PENALTIES
        (PAYMENTNO     INTEGER NOT NULL,
         PLAYERNO      INTEGER NOT NULL,
         PAYMENT_DATE  DATE NOT NULL,
         AMOUNT        DECIMAL(7,2) NOT NULL,
         PRIMARY KEY  (PAYMENTNO),
         FOREIGN KEY  (PLAYERNO) REFERENCES PLAYERS (PLAYERNO)
            ON DELETE SET NULL)

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.

Note

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.


A foreign key may use different actions for the two statements. For example, you can define a foreign key with the referencing actions ON UPDATE RESTRICT and ON DELETE CASCADE.

Portability

Not every SQL product supports referencing actions.


Exercise 16.9:

Not specifying referencing actions is equal to specifying which referencing actions?

Exercise 16.10:

Which update restrictions are imposed by the following definition?

CREATE   TABLE MATCHES
        (MATCHNO      SMALLINT NOT NULL,
         TEAMNO       SMALLINT NOT NULL,
         PLAYERNO     SMALLINT NOT NULL,
         WON          SMALLINT NOT NULL,
         LOST         SMALLINT NOT NULL,
         PRIMARY KEY  (MATCHNO),
         FOREIGN KEY  (TEAMNO)
            REFERENCES TEAMS
            ON UPDATE CASCADE
            ON DELETE RESTRICT,
         FOREIGN KEY  (PLAYERNO)
            REFERENCES PLAYERS
            ON UPDATE RESTRICT
            ON DELETE CASCADE )



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows