sql / expert
Snippet
Relational Invariant Maintenance via Action Policies
Action policies on foreign keys define the automated behavior of the database when a referenced primary key is modified or deleted. Using SET DEFAULT or CASCADE ensures structural integrity and data consistency without relying on manual trigger logic or application-level checks.
snippet.sql
1
2
3
4
5
6
ALTER TABLE project_assignmentsADD CONSTRAINT fk_employeeFOREIGN KEY (emp_id)REFERENCES employees (id)ON DELETE SET DEFAULTON UPDATE CASCADE;
Breakdown
1
ON DELETE SET DEFAULT
Automatically resets the child column to its defined default value if the parent record is removed.
2
ON UPDATE CASCADE
Propagates primary key updates to all referencing child records automatically to maintain reference parity.