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:
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:
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.
Since Template must be related to a Region, you need a foreign key from
Template.region_id will be NOT NULLable.
Template.country_id will be NULLable.
Make a unique constraint on the composite key
Then you can create a foreign key from
(Template.country_id , Template.region_id) to
This ensures that if the Template is related to a Country, then the
Template.region_id is the same as the related
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
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.