RE: Strange behavior on multiple primary key behavior d eleting childr en

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