1.11 Referential Integrity

Question 1

fully NULL

Answer 1

A fully NULL foreign key is a simple or composite foreign key in which all columns are NULL.

Question 2

Referential integrity

Answer 2

Referential integrity is a relational rule that requires foreign key values are either fully NULL or match some primary key value.

Question 3

RESTRICT constraint

Answer 3

RESTRICT rejects an insert, update, or delete that violates referential integrity.

Applies to primary key update and delete, and foreign key insert and update

Question 4

SET NULL constraint

Answer 4

SET NULL sets invalid foreign keys to NULL.

Applies to primary key update and delete, and foreign key insert and update

Question 5

SET DEFAULT constraint

Answer 5

SET DEFAULT sets invalid foreign keys to the foreign key default value.

Applies to primary key update and delete, and foreign key insert and update

Question 6

CASCADE constraint

Answer 6

CASCADE propagates (extends/applies) primary key changes to foreign keys.

Applies to primary key update and delete only.

Question 7

ON UPDATE / ON DELETE

Answer 7

Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint. ON UPDATE and ON DELETE are followed by either RESTRICT, SET NULL, SET DEFAULT, or CASCADE.

Question 8

Referential integrity violations for composite foreign keys

Answer 8

Primary key is updated or deleted.
Foreign key is updated or inserted

Question 9

What does CASCADE do when a primary key is deleted?

Answer 9

Rows containing matching foreign keys are deleted.

Question 10

What does CASCADE do if a primary key is updated?

Answer 10

matching foreign keys are updated to the same value.