Re: [PG9.1] CTE usage

On 16 September 2013 11:58, Ladislav Lenart <lenartlad(at)volny(dot)cz> wrote:
> Hello all.
>
> I am curious about the following usage of CTEs:
>
> Imagine three tables:
> * item (id, item_type1_id, item_type2_id, ...)
> * item_type1 (id, ...)
> * item_type2 (id, ...)
> where
> * item_type1_id is FK to item_type1 (id)
> * item_type2_id is FK to item_type2 (id)
>
> Items are of two types (type1 and type2). Each item type has different data
> columns. An item is either of type1 (item_type1_id is populated) or of type2
> (item_type2_id is populated). I want to delete some items along with the
> corresponding rows in the tables item_type1 and item_type2 (they have no meaning
> without the 'parent'). I have written the following CTE (I want to compute
> items_to_delete only once):

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.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.