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!