Re: Foreign Key 'walker'?

Erwin Moller wrote:

> No, that is not the kind of chicken I was talking about. ;-)
> My chicken is more along these lines:
> I often have some tables to which everything is related (eg tblcourse
> that contains everything belonging to a certain course).
> I don't want to make a single simple mistake that if I accidentally
> delete an entry there, I lose all underlying data via the CASCADE.

OK, so the idea is to prevent DELETEs of records with existing
relationships, except when invoked via some sort of script or wrapper
that says, essentially, "Yes, I really do mean delete this record and
all its related records, I'm not just accidentally issuing a DELETE".

Personally, if I had to do this I'd do this with a PL/PgSQL function
that dug through pg_catalog or INFORMATION_SCHEMA to do a depth-first
search and delete of the related records. Frankly, though, it sounds
horrible, and if you run into a relationship loop you're in a real mess.
At least the latter problem can't bite you unless you use DEFERRED
constraints.

> No problem at all.
> I totally agree with you.
> I only have this fear I screw up (not Postgresql) if I use CASCADE and
> accidentally delete a 'high' record in the chain.

To me, that sounds like you might have some of your relationships
backwards. Generally I wouldn't want to set an ON DELETE CASCADE
relationship on a parent record (not does it usually make any sense) ;
rather, the relationship on the child record will have ON DELETE CASCADE
set so the child will be deleted if the parent is. Deleting a child
record should only delete the child record, never cascade up to a parent.

The child record is useless and meaningless without the parent, so this
is appropriate.

For a practical example in a course/student management tool: If you
delete a `student', and the student has `student_course' (an m:n mapping
table) entries referencing `course', you would not expect the course to
be deleted, only the student<->course relationship and the student. If
the course was deleted explicitly by the user, you'd expect the
student_course relationship to restrict the deletion if students were
still listed as taking the course. So, the natural definition would be:

CREATE TABLE student_course (
    student_id INTEGER REFERENCES student(student_id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES course(course_id) ON DELETE NO ACTION,
    PRIMARY KEY(student_id, course_id)
);

... which is pretty close to what you end up with if you just bang out
the obvious structure for the relationship.

There are odd cases where those relationships end up being reversed (or
at least bidirectional), and in those cases I do tend to avoid ON DELETE
CASCADE, instead providing functions, triggers or rules to clean up
appropriately.

--
Craig Ringer