Help defining a chained one-to-many relationship tables

by djfried   Last Updated August 13, 2019 22:06 PM - source

Sorry if the title is confusing but I couldn't think of a way to properly word this question without laying out what I am trying to do.

Basically I have 3 tables and I'm trying to figure out how to best relate them all given the constraint that each Template should have a region but necessarily doesn't have to country.

 Template
  id
  name
  description
  country_id

 Country
  id
  name
  region_id

 Region
  id
  name

Two solutions that I've come up with but am not really satisfied with are:

  1. add region_id column to template table. The problem I have with this solution is creating the circular relationship between the region_id in country and in template. Enforcing that region_id is the same in country and template tables also seems difficult to enforce.
  2. add region specific country records to the country table that have no real country value but have a relationship to an existing region. For example:

    Country: id:5 name: North America region: 1 -> North America

Solution 2 seems the simplest but I also feel like its not the most elegant solution because I am creating these "fake" country records.



Answers 1


Since Template must be related to a Region, you need a foreign key from Template.region_id to Region.id

Template.region_id will be NOT NULLable.

Template.country_id will be NULLable.

Make a unique constraint on the composite key (Country.id, Country.region_id)

Then you can create a foreign key from (Template.country_id , Template.region_id) to (Country.id, Country.region_id)

This ensures that if the Template is related to a Country, then the Template.region_id is the same as the related Country.region_id

Luckily (for you) when a row's composite foreign key contains one or more null values, then the row is "valid" regardless of the existence of rows in the referenced table. So having a NULL Template.country_id and a NOT NULL Template.region_id will be fine.

One interesting side effect of this "doubly constrained" system is that it is difficult to update the region_id column of a Country. Any attempt to update Country.region_id will cause a constraint violation if there are any Templates that contain references to that Country, since the (Template.country_id , Template.region_id) pair will not refer to an existing (Country.id, Country.region_id).

If you must allow this type of update, then you can use an ON UPDATE CASCADE clause when creating the (Template.country_id , Template.region_id) foreign key.

John Rees
John Rees
August 13, 2019 22:25 PM

Related Questions



Can't log in to oracle 11g R2 dbconsole

Updated May 13, 2015 20:02 PM

Encapsulate table with view

Updated March 10, 2016 07:02 AM


Can't open oracle 11g admin page

Updated May 13, 2015 15:02 PM