Re: Cascaded updates / deletes don't work on inherited - Mailing list pgsql-novice

Yep, you're right. Inheritance doesn't really mix well with integrity
constraints (or so say the gurus like Josh Berkus and Tom Lane if you
search the archives). This means that uniqueness constraints are not
enforced across inheritance trees. Each table may have it's own uniqueness
constrains but the tree as a whole does not.

I do something similar for an application I'm working on. I get around
this by exporting my primary key to another table and everything relates
to *that*. For example SoSPeople is the parent and contains no real
records. I have 87 child tables (one for each county of Minnesota) which
exist to partition the 2.5 million records into smaller chunks. Now if I
want to use a primary key I have a supplementary table SoSIDs which is a
single column wide and only contains the primary key data. Other tables
relate to the ID table and the SoSPeople tables also relate to it (since
it is representative of them).

You'll want to decide how to handle deletes, updates and insertions. I do
all of my update work to the single parent SoSPeople and use triggers to
maintain the ID table, re-insert the record into a child table, delete the
ID from the ID table etc. Check out the app on my web page at
http://www.greentechnologist.org/political/Voter/ (and the last part is
dev or something like that). Just browse.

Joshua b. Jore
http://www.greentechnologist.org

On Mon, 6 May 2002, Michael Barber wrote:

> If I create a table with a primary key and then I reference that column
> from another table, the cascaded updates and deletes work without any
> problem.
>
> However, if I then create a table that inherits the previous one, the
> cascaded deletes and updates only affect the original (parent) table --
> the records in the new (child) table remain unaffected.
> To be honest, I really don't know all that much about SQL standards, but I
> would have thought that since just about all the characteristics of the parent
> table are inherited, then surely the triggers would be as well.
>
> If I create a table with a primary key and then I reference that column
> from another table, the cascaded updates and deletes work without any
> problem.
>
> However, if I then create a table that inherits the previous one, the
> cascaded deletes and updates only affect the original (parent) table --
> the records in the new (child) table remain unaffected.
>
> To be honest, I really don't know all that much about SQL standards, but I
> would have thought that since just about all the characteristics of the parent
> table are inherited, then surely the triggers would be as well.
>
> Apart from that, I'm not too sure how to perform the cascaded deletes or
> updates with a SQL instruction, so if someone could help me with that,
> I'd really appreciate it. There must be a 'standard' way to do it ;-)
>
> Michael S. Barber
>  |
>  | mbarber@netraver.org.za
>  | ICQ 3233720
>  | +27 (0)11 7875110 (landline)
>  | +27 (0)83 2260428 (cellular)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>