Question

Describe the three main types of referential actions (cascade, restrict, set null). How do you use these in your work?

Answer 1

cascade :

It means that no action is performed with the child data when the parent data is deleted or updated. CASCADE. It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is either deleted or updated when the parent data is deleted or updated.

ON DELETE CASCADE / ON UPDATE CASCADE

When a referenced foreign key is deleted or updated, all rows referencing that key are deleted or updated, respectively. If there are other alterations to the row, such as a SET NULL or SET DEFAULT, the delete will take precedence. Note that CASCADE does not list objects it drops or updates, so it should be used cautiously.

restrict

ON DELETE NO ACTION	Default action. If there are any existing references to the key being deleted, the transaction will fail at the end of the statement. The key can be updated, depending on the ON UPDATE action. Alias: ON DELETE RESTRICT
ON UPDATE NO ACTION	Default action. If there are any existing references to the key being updated, the transaction will fail at the end of the statement. The key can be deleted, depending on the ON DELETE action. Alias: ON UPDATE RESTRICT
ON DELETE RESTRICT / ON UPDATE RESTRICT	RESTRICT and NO ACTION are currently equivalent until options for deferring constraint checking are added. To set an existing foreign key action to RESTRICT, the foreign key constraint must be dropped and recreated.

set null :

ON DELETE SET NULL / ON UPDATE SET NULL

When a referenced foreign key is deleted or updated, respectively, the columns of all rows referencing that key will be set to NULL. The column must allow NULL or this update will fail.

ON DELETE SET DEFAULT / ON UPDATE SET DEFAULT

When a referenced foreign key is deleted or updated, the columns of all rows referencing that key are set to the default value for that column. If the default value for the column is null, or if no default value is provided and the column does not have a NOT NULL constraint, this will have the same effect as ON DELETE SET NULL or ON UPDATE SET NULL. The default value must still conform with all other constraints, such as UNIQUE.