RE: Strange behavior on multiple primary key behavior d eleting childr en - Mailing list pgsql-hackers

On Fri, 8 Jun 2001, Mike Cianflone wrote:

>>     I'm running into some strange behavior with foreign keys which are a
>> tuple of primary keys.
>> 
>> 
>> I have a parent table sector, and a child of that is cell_area table and
a
>> child of that is unit table.
>> 
>> The cell_area table has a foreign key parent_sector_index referencing
same
>> name in parent table sector.
>> 
>> The unit table has a foreign key, parent_cell_area_index, and
>> parent_sector_index referencing same names in its parent sector and
>> cell_area.
>> 
>> The primary key of each table is the composite of the foreign keys as
well
>> as it's own index, therefore it's possible to have, for example, in the
>> cell-area table, to have several entries of the same index, say 1, as
long
>> as the parent_sector_index is different for each. So we could have for
the
>> cell_area table (1,1) (1,2) (1,3), as the primary key tuple.
>> 
>> The same thing applies to the lowest level table, the unit table, which
is a
>> 3 tuple of its own index, plus the parent_cell_area_index, plus the
>> parent_sector_index.
>> 
>> Cascading deletes are turned off, and I have implemented my own trigger
that
>> will delete the children, say for example when the cell_area is deleted,
my
>> trigger will delete the children in the unit table, that have the same
>> parent_sector_index, and that have that specific cell_area as its
>> parent_cell_area.
>> 
>>     Here's the problem. If there are more than one entry in the
>> cell_area table with the same index, then I receive a referential
integrity
>> violation when I try to remove the cell_area of (1,1), even though, based
>> upon the primary key tuple as explained above, there are no children that
>> reference it.
>>     For example, if I have in the cell_area table (cell_area_index,
>> parent_sector_index) and the values are (1,1) (1,2) (1,3), and have in
its
>> child table which is the unit table (unit_index, parent_cell_area_index,
>> parent_sector_index) and the values (1, 1, 2) (1,1,3), so that those 3
>> tuples refer to items 2 and 3 of the set shown in the first part of this
>> paragraph, and none refer to the first item which is (1,1), then when I
try
>> to delete the cell_area of (1,1) I get a referential integrity violation
>> because it sees that the child which is the unit table has foreign keys
>> referencing the cell_area_index of 1 which is the same cell_area_index I
am
>> deleting. But note that ALL of the items still in cell_area also have
their
>> cell_area_index at 1, so the referential integrity constraint should not
>> fail since they are still referring to that "1". Also note that the other
>> foreign keys in the children are not referencing any other of the tuples
in
>> the parent, so the item I am trying to delete is not being referenced by
>> anything.

>How is the unit table references created?
>Are they:
>(1)
> cell_area_index -> cell_area(cell_area_index)
> parent_sector_index-> sector(parent_sector_index)
>
>(2)
> (cell_area_index, parent_sector_index) -> cell_area(c_a_i, p_s_i)
> parent_sector_index-> sector(parent_sector_index)
>
>
>If 1, then what version are you running.  That's not technically a legal
>references constraint, but that wasn't checked under 7.0.x.  The target
>cols of the constraint *MUST* belong to a unique or primary key constraint
>that have no additional columns.  Try 2 instead.
>
>If 2, can you send the schema and data file to set this up from start
>state?
>Yes, they were referenced as in your example #1. I'm running version
7.0.3.2. I changed the foreign keys to reference the parents as you have
specified in your example #2 and that fixed the problem.Thank you for taking the time to read through my long winded
issue
and make sense of it. I removed my own home-grown cascading delete triggers
now that this works fine. Thanks!

Mike Cianflone