Re: Foreign Key 'walker'?

Erwin Moller schreef:
> Craig Ringer schreef:
>> 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.

[Pfft my english sucks, I added a few clarifications]
> Hi Craig,
>
> Thanks for your reply.
>
> For clearity's sake: If I want to delete a record that is refered to,
> I always simply first delete the 'lower' records that fit my criteria,
> then the 'higher'.
> So I often end up with a series of deletes, which when executed in
> that right order, do the same as a CASCADE would do on the 'higher'
> record.
So that is the way I prefer to do the deletions.
>
> Reason is simply I rather hit a FK constraint than a cascading delete
> on mistake.
> (I am programming against postgresql in almost all my projects, thus
> this mistake just happens from time to time.)
>
> So, that is the way I prefer doing it: It keeps me sharp because I
> force myself to always understand each relation in every table that in
> in 'the chain'.
> So I am NOT looking for help on writing such a wrapper/script/function
> to do this, because I prefer doing it myself.
>
> I do not mind making a few deletes (allthough they get more and more
> complex if you have more levels).
> I asked this weird question because it would come in handy if I could
> get the list of tables that are connected via FK to my table in question.
>
> Is this clear? Maybe I have a weird way of programming. ;-)
>
>>
>>> 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.
> I must have written very poorly, since that is NOT what I mean.
> (I am not a native english speaker, so indulge me please).
>
> I totally agree with the statement that a DELETE should NEVER cascade
> up to the parent record, and I'll never design a database like that.
>
> Possibly my poor understanding of CASCADE is the root of this
> confusion. (I wrote already I never use it).
> I thought that is I define a field in a table with 'ON DELETE CASCADE'
> that means that ANY record in other tables that have a FK constraint
> on this parenttable are also deleted.
> And the same for child-child-tables, etc.
Typo again: The 'ON DELETE CASCADE' is not for a column (field) but for
the table.

Sorry to be so sloppy. ;-)

Regards,
Erwin Moller
>
> Do I have that right?
>
>>
>> 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.
> Well, that is excactly the way I work too. So we agree here.
>
> To stick to this example: I was describing the situation I accidently
> DELETED course_id in table course.
> I don't want that that deletion cascades though the whole database and
> deletes all related rows.
>
> But maybe I misinterpret the way CASCADE works (see my explanation
> above).
>
> Regards,
> Erwin Moller
>
>>
>> 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
>>
>>
>>
>
>