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