Re: Foreign Key 'walker'?

Shane Ambler schreef:
> ries van Twisk wrote:
>>
>> On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote:
>>
>>> Hi group,
>>>
>>> Considering following (simplified) example:
> <snip>
>>>
>>> Suppose I want to delete a record in tblnr1.
>>> Does Postgres has some command/procedure/function to list tables
>>> that have FK constraints on that table (tblnr1)
>
> The data you are looking for is stored in the system catalogs.
> http://www.postgresql.org/docs/8.3/interactive/catalogs.html
> You should be able to come up with some SELECT's to get what you want.
>
>>> and lists also the tables that have a FK constraint on tables that
>>> have a FK constraint on the first? etc.
>>> So I would like some kind of FK 'walker'.
>>>
>>> I want this because:
>>> 1) I hate DELETE CASCADE because I am chicken (So I use a script to
>>> delete all related records in the right order in a transaction)
>
> That isn't being chicken it is being silly (or is that just stubborn ;).
> PostgreSQL is designed to delete related records that you tell it to
> delete. Let it do what it is suppose to do.
Hi,

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.
That is why I decided never to use CASCADE, and simply do it by myself.
No big deal except that I have to find out the related tables.
I rather have a FK constraint violation error than an empty DB.

Hence my question.
I am not afraid that Postgres will screw up somehow.
That actually NEVER happened in all the years I am using it. Try that
with MSSQL or MySQL. I love Postgres. ;-)

>
>>> 2) I have a lot of tables and am afraid I miss some. And I am also a
>>> bit lazy .-)
>
> If your lazy why do all this manual work when you can leave it
> automated? It won't miss a related record after you tell it to cascade
> delete. This is an old well tested feature that you can rely on.
I described above.
>
>
> That probably all sounds more aggressive than it should. Not having a
> strong dig at you but I do want to emphasise the fact that you
> shouldn't waste your time doing manually what the software is designed
> to do.
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.

Regards,
Erwin Moller