Thread: delete cascade not working

My noob understanding is that deleteing one of these:

CREATE TABLE provider_input.file_load
(
   sid serial NOT NULL,
   file_name_full text,
   file_name text,
   file_creation_date text,
   load_universal_time numeric,
   headers text,
   date timestamp without time zone DEFAULT now(),
   CONSTRAINT file_load_pkey PRIMARY KEY (sid)
)

Given constraint:

ALTER TABLE provider_input.common
   ADD CONSTRAINT common_pin_file_load_sid_fkey FOREIGN KEY
(pin_file_load_sid)
       REFERENCES provider_input.file_load (sid) MATCH FULL
       ON UPDATE NO ACTION ON DELETE CASCADE;


Would cause any of these referring to the file_load to be deleted:

CREATE TABLE provider_input.common
(
   sid serial NOT NULL,
   pin_file_load_sid integer,
   load_row_no integer,
   CONSTRAINT common_pin_file_load_sid_fkey FOREIGN KEY (pin_file_load_sid)
       REFERENCES provider_input.file_load (sid) MATCH FULL
       ON UPDATE NO ACTION ON DELETE CASCADE
)

...but I just tried it and the file_load is gone but not the items that
referenced it. Two things that might matter:

    I am using pgAdminIII to do the delete

I do not actually instantiate common, I have a table that inherits from
that. I will play around now to see if that is an issue, thought I'd
send up a flare here at the same time.

kt

On Sat, Apr 25, 2009 at 04:01:20PM -0400, Kenneth Tilton wrote:
> Given constraint:
>
> ALTER TABLE provider_input.common
>   ADD CONSTRAINT common_pin_file_load_sid_fkey FOREIGN KEY
> (pin_file_load_sid)
>       REFERENCES provider_input.file_load (sid) MATCH FULL
>       ON UPDATE NO ACTION ON DELETE CASCADE;

<snip>

> I do not actually instantiate common, I have a table that inherits from
> that. I will play around now to see if that is an issue, thought I'd
> send up a flare here at the same time.

First guess: the foreign key constraint needs to exist on the child
table; having the key on the parent means nothing for the child table.

- Josh / eggyknap

Joshua Tolley wrote:
> On Sat, Apr 25, 2009 at 04:01:20PM -0400, Kenneth Tilton wrote:
>> Given constraint:
>>
>> ALTER TABLE provider_input.common
>>   ADD CONSTRAINT common_pin_file_load_sid_fkey FOREIGN KEY
>> (pin_file_load_sid)
>>       REFERENCES provider_input.file_load (sid) MATCH FULL
>>       ON UPDATE NO ACTION ON DELETE CASCADE;
>
> <snip>
>
>> I do not actually instantiate common, I have a table that inherits from
>> that. I will play around now to see if that is an issue, thought I'd
>> send up a flare here at the same time.
>
> First guess: the foreign key constraint needs to exist on the child
> table;

I just confirmed that a common row gets deleted but not the row from the
class that inherits from common.


> having the key on the parent means nothing for the child table.

But I am "inheriting" from the parent, so everything about the parent
should be true for me unless overridden. I mean in the usual sense of
inherit I know from OO.

The only thing I see in the doc that is relevant is about multiple
inheritance where it talks about columns duplicated in parents and says
"all constraints provided from any declaration are merged together and
all are applied to the new table" so it seems to me pg understands that
at least some constraints should be inherited.

kt

On Sat, Apr 25, 2009 at 04:21:06PM -0400, Kenneth Tilton wrote:
> But I am "inheriting" from the parent, so everything about the parent
> should be true for me unless overridden. I mean in the usual sense of
> inherit I know from OO.

This is from the current docs
(http://www.postgresql.org/docs/current/static/ddl-inherit.html):

"All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints
(unique, primary key, and foreign key constraints) are not inherited."

Although it's easy to imagine otherwise, really inheritance structures
are made up of individual tables, and for the most part behave no
differently from any other tables. Indexes, constraints, etc. on the
parent table do not apply to data in child tables. The documentation
snippet above really means that when a new child table is created, check
constraints and not null constraints are automatically created to
correspond with those on the parent. Don't let yourself start thinking
inheritance in PostgreSQL behaves too similarly to inheritance in, say,
most programming language. Inheritance works nicely for partitioning;
most people that try to apply table inheritance to the same sorts of
scenarios where they'd apply inheritance in a programming language come
to find that it's more of a pain that it's worth.

- Josh / eggyknap