Thread: Strange behavior on multiple primary key behavior deleting childr en

    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.
I have tried turning on DEFERRED constraint checks so that it would
not check to see that that index of "1" was being deleted but still same
problem.
If I leave everything else the same but remove the parent_cell_area
from the unit table as a foreign key (this removing the check for
referential integrity on that one column only), then it deletes just fine.
It seems that the check for referential integrity doesn't check to
see that there are other items in the parent columns that have the same
value which would still let the integrity pass.

Any comments? Please ;-)

Mike

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?