[5] Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table.
Because the primary key of a table must be unique, and because CUSTOMERID only contains values from that primary key field, we may assume that, when it has a value, CUSTOMERID will identify the particular customer which placed the order.
Many real world databases work around this problem by 'inactivating' rather than physically deleting master table foreign keys, or by complex update programs that modify all references to a foreign key when a change is needed.
Foreign keys play an essential role in database design.
In database management systems, this is often accomplished by linking a first and second reference to the same table.
Likewise, foreign keys can be defined as part of the CREATE TABLE SQL statement.
Because the database management system enforces referential constraints, it must ensure data integrity if rows in a referenced table are to be deleted (or updated).
SQL:2003 specifies 5 different referential actions that shall take place in such occurrences: Whenever rows in the parent (referenced) table are deleted (or updated), the respective rows of the child (referencing) table with a matching foreign key column will be deleted (or updated) as well.
Similarly, a row cannot be deleted as long as there is a reference to it from a referencing or child table.
To understand RESTRICT (and CASCADE) better, it may be helpful to notice the following difference, which might not be immediately clear.
In other words, when an UPDATE or DELETE statement is executed on the referenced table using the referential action NO ACTION, the DBMS verifies at the end of the statement execution that none of the referential relationships are violated.
Using NO ACTION, the triggers or the semantics of the statement itself may yield an end state in which no foreign key relationships are violated by the time the constraint is finally checked, thus allowing the statement to complete successfully.