So I have the following question on my assignment that I've been stuck on for a while now:
when employee S is deleted
i. set FKs in Employee and Department to null
ii. delete referenced rows in Works_On and Dependent
I've been thinking that I should use a CASE statement for this, but I'm not sure how I should go about structuring the SQL. I am using Postgresql.
The following link is a screenshot of my database structure:
Have you read the PostgreSQL manual on FOREIGN KEY constraints?
Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.
There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted.
CREATE TABLE works_on ( essn ... REFERENCES employee(ssn) ON DELETE SET NULL, pno ..., hours ... );
I can't guess at the data types for your columns from your screenshot. But the excerpt and link to the manual, and that partial code example, should give you what you need to know.