On 16.9.2013 13:26, Alban Hertroys wrote: > On 16 September 2013 11:58, Ladislav Lenart <lenartlad@xxxxxxxx> 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. 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. Could you please describe your proposal in more detail (just the list of tables with their columns)? Nevertheless, I am still curious about my original question(s): * Whether is this style of CTE usage discouraged (i.e. rely on the in-order evaluation of CTEs without even mentioning them in the top query). * Any idea what could be wrong in my example. Thank you, Ladislav Lenart -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
