Re: Foreign Key 'walker'?

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.
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.

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.

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
>
>
>