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,
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.