Access foreign key not updating
SET DEFAULT All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is updated or deleted.
For this constraint to execute, all foreign key columns must have default definitions.
For this constraint to execute, the foreign key columns must be nullable.
A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references).
Sales Person table, and the salesperson's ID is used for sales orders in the Sales.
Sales Order Header table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the Sales Order Header table without a link to the data in the Sales Person table. The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table.
Because primary key constraints guarantee unique data, they are frequently defined on an identity column.
When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns.