1

I have tables for country/province/district/commune/village. Each have a foreign key on its parent with a cascade delete.

I then have a location table with foreign keys to each tables above with a cascade SET NULL

When deleting a district, I get a foreign key violation on village_id in table location; Deleting a province, a commune or a village work as expected. Setting that one particular constraint to DEFERRED solves the issue.

Please see table definition below. I can't find what is wrong with it. Couldn't find known bugs either.

     Table "public.province"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 id         | integer | not null
 country_id | integer | 
Indexes:
    "province_pkey" PRIMARY KEY, btree (id)
    "idx_4adad40bf92f3e70" btree (country_id)
Foreign-key constraints:
    "fk_4adad40bf92f3e70" FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE CASCADE
Referenced by:
    TABLE "province_translation" CONSTRAINT "fk_274f5ebf2c2ac5d3" FOREIGN KEY (translatable_id) REFERENCES province(id) ON DELETE CASCADE
    TABLE "district" CONSTRAINT "fk_31c15487e946114a" FOREIGN KEY (province_id) REFERENCES province(id) ON DELETE CASCADE
    TABLE "location" CONSTRAINT "fk_5e9e89cbe946114a" FOREIGN KEY (province_id) REFERENCES province(id) ON DELETE CASCADE

.

      Table "public.district"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 id          | integer | not null
 province_id | integer | 
Indexes:
    "district_pkey" PRIMARY KEY, btree (id)
    "idx_31c15487e946114a" btree (province_id)
Foreign-key constraints:
    "fk_31c15487e946114a" FOREIGN KEY (province_id) REFERENCES province(id) ON DELETE CASCADE
Referenced by:
    TABLE "location" CONSTRAINT "fk_5e9e89cbb08fa272" FOREIGN KEY (district_id) REFERENCES district(id) ON DELETE SET NULL
    TABLE "district_translation" CONSTRAINT "fk_98db6e562c2ac5d3" FOREIGN KEY (translatable_id) REFERENCES district(id) ON DELETE CASCADE
    TABLE "commune" CONSTRAINT "fk_e2e2d1eeb08fa272" FOREIGN KEY (district_id) REFERENCES district(id) ON DELETE CASCADE

.

      Table "public.commune"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 id          | integer | not null
 district_id | integer | 
Indexes:
    "commune_pkey" PRIMARY KEY, btree (id)
    "idx_e2e2d1eeb08fa272" btree (district_id)
Foreign-key constraints:
    "fk_e2e2d1eeb08fa272" FOREIGN KEY (district_id) REFERENCES district(id) ON DELETE CASCADE
Referenced by:
    TABLE "village" CONSTRAINT "fk_4e6c7faa131a4f72" FOREIGN KEY (commune_id) REFERENCES commune(id) ON DELETE CASCADE
    TABLE "location" CONSTRAINT "fk_5e9e89cb131a4f72" FOREIGN KEY (commune_id) REFERENCES commune(id) ON DELETE SET NULL
    TABLE "commune_translation" CONSTRAINT "fk_a276643d2c2ac5d3" FOREIGN KEY (translatable_id) REFERENCES commune(id) ON DELETE CASCADE

.

      Table "public.village"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 id         | integer | not null
 commune_id | integer | 
Indexes:
    "village_pkey" PRIMARY KEY, btree (id)
    "idx_4e6c7faa131a4f72" btree (commune_id)
Foreign-key constraints:
    "fk_4e6c7faa131a4f72" FOREIGN KEY (commune_id) REFERENCES commune(id) ON DELETE CASCADE
Referenced by:
    TABLE "location" CONSTRAINT "fk_5e9e89cb5e0d5582" FOREIGN KEY (village_id) REFERENCES village(id) ON DELETE SET NULL
    TABLE "village_translation" CONSTRAINT "fk_c84431b62c2ac5d3" FOREIGN KEY (translatable_id) REFERENCES village(id) ON DELETE CASCADE

.

               Table "public.location"
   Column    |      Type      |       Modifiers       
-------------+----------------+-----------------------
 id          | integer        | not null
 country_id  | integer        | not null
 province_id | integer        | not null
 district_id | integer        | 
 commune_id  | integer        | 
 latitude    | numeric(20,10) | default NULL::numeric
 longitude   | numeric(20,10) | default NULL::numeric
 village_id  | integer        | 
Indexes:
    "location_pkey" PRIMARY KEY, btree (id)
    "idx_5e9e89cb131a4f72" btree (commune_id)
    "idx_5e9e89cb5e0d5582" btree (village_id)
    "idx_5e9e89cbb08fa272" btree (district_id)
    "idx_5e9e89cbe946114a" btree (province_id)
    "idx_5e9e89cbf92f3e70" btree (country_id)
Foreign-key constraints:
    "fk_5e9e89cb131a4f72" FOREIGN KEY (commune_id) REFERENCES commune(id) ON DELETE SET NULL
    "fk_5e9e89cb5e0d5582" FOREIGN KEY (village_id) REFERENCES village(id) ON DELETE SET NULL
    "fk_5e9e89cbb08fa272" FOREIGN KEY (district_id) REFERENCES district(id) ON DELETE SET NULL
    "fk_5e9e89cbe946114a" FOREIGN KEY (province_id) REFERENCES province(id) ON DELETE CASCADE
    "fk_5e9e89cbf92f3e70" FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE CASCADE
Referenced by:
    TABLE "location_translation" CONSTRAINT "fk_a2bca22b2c2ac5d3" FOREIGN KEY (translatable_id) REFERENCES location(id) ON DELETE CASCADE
    TABLE "harvest_announcement" CONSTRAINT "fk_b13400db64d218e" FOREIGN KEY (location_id) REFERENCES location(id)

.

# select * from location;
 id  | country_id | province_id | district_id | commune_id | latitude | longitude | village_id 
-----+------------+-------------+-------------+------------+----------+-----------+------------
 542 |        731 |        1091 |             |            |          |           |           
 541 |        731 |        1090 |        1090 |       1095 |          |           |        313
(2 rows)


# select * from village;
 id  | commune_id 
-----+------------
 313 |       1095
 314 |       1097


# delete from province where id=1090;
DELETE 1

# delete from commune where id = 1095;
DELETE 1

# delete from village where id = 313;
DELETE 1

# delete from district where id = 1090;
ERROR:  insert or update on table "location" violates foreign key constraint "fk_5e9e89cb5e0d5582"
DETAIL:  Key (village_id)=(313) is not present in table "village".

# alter table location drop constraint fk_5e9e89cb5e0d5582;ALTER TABLE
# ALTER TABLE location ADD CONSTRAINT FK_5E9E89CB5E0D5582 FOREIGN KEY (village_id) REFERENCES village (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
# delete from district where id = 1090;
DELETE 1
  • Besides the foreign key violations you may get from the cascade actions, you design has another flaw: You may end up with data in location that reference a country, a province, a district and a village that have no connection between them. E.g. a province that is not part of that country. Or a village that is not part of that district or a district that is not part of that province, etc. Or even all of the above inconsistencies. – ypercubeᵀᴹ Oct 8 '16 at 11:00
  • Example: nothing in the foreign keys forbids you from inserting a location with the district of Paris, the province of Wisconsin and the country of Japan. I'm sure there is no Wisconsin in Japan (not sure about Paris in Wisconsin ;) – ypercubeᵀᴹ Oct 8 '16 at 11:03
  • Thanks for your comment. I am aware of this issue. The design is motivated by the fact that locations can be of any granularity. Data integrity will have to be ensured by the application (although I guess it could be enforced at the database level with some kind of triggers). – Jerome B Oct 10 '16 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.