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.
Monday, May 22, 2006
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment