Re: Strange behavior on multiple primary key behavior deleting childr en
От | Stephan Szabo |
---|---|
Тема | Re: Strange behavior on multiple primary key behavior deleting childr en |
Дата | |
Msg-id | Pine.BSF.4.21.0106081919330.28211-100000@megazone23.bigpanda.com обсуждение исходный текст |
Ответ на | Strange behavior on multiple primary key behavior deleting childr en (Mike Cianflone <mcianflone@littlefeet-inc.com>) |
Список | 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?
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Thomas LockhartДата:
Сообщение: Re: Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards
Следующее
От: Tom LaneДата:
Сообщение: Re: Re: [PATCHES] Fw: Isn't pg_statistic a security hole - Solution Proposal