Re: [PG9.1] CTE usage

On 16.9.2013 15:50, Adrian Klaver wrote:
> On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
>> On 16.9.2013 13:26, Alban Hertroys wrote:
>
>>>
>>> Wouldn't it be much easier to define an FK constraint with ON DELETE CASCADE?
>>> With that, you only need to worry about which rows you delete from the
>>> parent table and dependant children will be removed automatically.
>>
>>
>> Hello.
>>
>> I don't quite follow. Having item.item_type1_id FK with ON DELETE CASCADE would
>> delete ITEM (the parent) when ITEM_TYPE1 (the child) is deleted. You suggests
>> the opposite direction.
>
> http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html
>
> "..In addition, when the data in the referenced columns is changed,
> certain actions are performed on the data in this table's columns. The
> ON DELETE clause specifies the action to perform when a referenced row
> in the referenced table is being deleted. .."
>
> "..CASCADE
> Delete any rows referencing the deleted row, or update the values of the
> referencing column(s) to the new values of the referenced columns,
> respectively.
> .."

Hello.

Thank you but I have read this in the official documentation before posting my
(previous) reply. So to quote the important bit about CASCADE:

Delete any rows referencing the deleted row

My example defines the table item with FK to the table item_type1 and FK to the
table item_type2. Specifying anything on these two constraints does not help one
bit when I delete an item, because item_type1 nor item_type2 does not reference
any... Therefore I suspect that Alban Hertroys had a different model in mind where:
* item would not have any FKs,
* item_type1 would have FK to item,
* item_type2 would have FK to item?

I just wasn't sure, hence I have asked him for a more detailed answer. However,
I am pretty sure ON DELETE CASCADE would not help in my current setup.

Ladislav Lenart