Re: Strange behavior on multiple primary key behavior deleting 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?