SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, May 22, 2006

Referential Integrity - Countries & States/Provinces

SQL Apprentice Question
I have a Country table with (surprise!) countries, and a Locale table
with states, territories, provinces, etc. I also have another table,
Port, that usually contains Country information but only occasionally
contains Locale information.

My question is, how do I set up the foreign keys maintain referential
integrity for Locale data but still allow for NULLs?


The following seems to fail whenever I have NULLs for the Country
and/or Locale of a Port:


CREATE TABLE Country
(
CountryID nchar(2) NOT NULL PRIMARY KEY,
Country nvarchar(50) NOT NULL
)


CREATE TABLE Locale
(
CountryID nchar(2) NOT NULL REFERENCES Country (CountryID),
LocaleID nvarchar(3) NOT NULL,
Locale nvarchar(100) NOT NULL,
PRIMARY KEY (CountryID, LocaleID)
)


CREATE TABLE Port
(
PortID smallint NOT NULL PRIMARY KEY,
Port nvarchar(100) NOT NULL,
SeaID tinyint NOT NULL REFERENCES Sea (SeaID),
CountryID nchar(2) NULL, --sometimes NULL!!!
LocaleID nvarchar(3) NULL, --often NULL!!!
FOREIGN KEY (CountryID, LocaleID) REFERENCES Locale (CountryID,
LocaleID)
)



Celko Answers
Look up the nested sets model and buidl a hierarchy of geography.
Also, ISO country codes are CHAR(3). The trick will be in having a
node type in the locations table and so constraints on the leaf nodes.

No comments: