Add a constraint to a table with self-join based on other fields in current and 'parent' records.

[pmhb2011]

Hi all,

I'm (still) playing / experimenting with some different table designs for heirarchical data.

I have made a little table:

Locations
---
LocationID
LocationName
LocationType (options = Country, State, Region, City)
LocationParentID (FK: Locations.LocationID)

Some dummy data:

LocationID	LocationName	LocationType	LocationParentID
01	USA	Country
02	California	State	01
03	Southern California	Region	02
04	Los Angeles	City	03
05	Bay Area	Region	02
06	San Jose	City	05
07	Australia	Country
08	Queensland	State	07
09	Far North	Region	08
10	Cairns	City	09

I would like to create a constraint that says:

• Countries cannot have parent locations (i.e., cannot have a LocationParentID)
• States, Regions, and Cities must have parent locations
• Cities cannot be parent locations
• States' parent locations must be countries
• Regions' parent locations must be states
• Cities' parent locations must be regions

Google and experimentation have not helped me.

Firstly, is this possible?

If yes, can anyone please show me how?

THANK YOU!

[orange]

You could set the LocationParentID to a known value ( eg. 00 top of hierarchy or -99 to signify unknown/unassigned ) and ensure your logic deals with this value. I have used this with hierarchy in many self join situations, but doesn't address all of your question. Example

As for a general approach( first thoughts), I think you would need a series of queries to validate the entries in your table.
For example, queries/code to ensure
-all cities have/reference a LocationType of Region
-all regions have/reference a LocationType of State
-all states have/reference a LocationType of Country

To check that
all Cities refer to a LocationParent of State and
all States refer to a LocationParent of Country and
all Regions refer to a LocationParent of State and
all Countries have a LocationParentID of 0 (all countries in my sample have LocationParentId = 0)

NOTE: This query identifies inconsistencies

SELECT MyLocationsHier.LocationID
    ,MyLocationsHier.LocationName
    ,MyLocationsHier.LocationType
    ,MyLocationsHier_1.LocationID
    ,MyLocationsHier_1.LocationType
FROM MyLocationsHier
INNER JOIN MyLocationsHier AS MyLocationsHier_1
    ON MyLocationsHier.LocationParentID = MyLocationsHier_1.LocationID
WHERE (
        (MyLocationsHier.LocationType = "City")
        AND (MyLocationsHier_1.LocationType <> "Region")
        OR (MyLocationsHier.LocationType = "Region")
        AND (MyLocationsHier_1.LocationType <> "State")
        OR (MyLocationsHier.LocationType = "State")
        AND (MyLocationsHier_1.LocationType <> "Country")
        OR (MyLocationsHier.LocationType = "Country")
        AND (MyLocationsHier_1.LocationParentId <> 0)

Just some info for consideration.

Your set up suggests
-regions are within a State. What exactly defines a region? NorthWest/PacificNorthwest or SouthEastern USA could include multiple States?
-cities are within region. Kansas City, Kansas, Kansas City, Missouri