Referential Integrity - Countries & States/Provinces

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)
)
Thanks!
-DanLook 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.|||"Dan Manes" <danthman@.cox.net> wrote in message
news:1148083925.869411.11720@.y43g2000cwc.googlegroups.com...
>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)
> )
>
Doesn't fail for me:
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)
)
go
insert into country values (1,'usa')
insert into locale values (1,'tx','Texas')
insert into port values (1,'Port Arthur',0,1,'tx')
insert into port values (2,'Houston',0,1,null)
insert into port values (3,'Dallas',0,null,null)
David|||Thanks, David. You're right. My problem was that what I thought were
NULLs in my source data table turned out to be blanks--not the same
thing. Once I replaced them with NULLs, everything was fine.
As for CELKO's comment about ISO Country codes being CHAR(3), there's
both a 2- and 3-character ISO standard. My example just happened to
have the 2-character version.
-Dan|||Go with the 3; the 2 is about to be deprecated becuase we just have too
many countries declaring themselves.|||If only it were that simple :) My real Country table actually has five
different code columns (including 2- *and* 3-digit ISO) because users
will be getting their country code data from several different
sources/databases, all of which use their own codes. Of course, some of
these source databases have country codes for entities the ISO doesn't
even consider countries.
The result, a big messy normalization problem. Don't you just love when
people ignore standards?
Anyway, even though it seems unlikely we'll go from 243 to 676
countries anytime soon, the 3-digit codes also have the advantage of
being more human readable. E.g., with two characters, Canada = CA and
China = CN; with three, Canada = CAN and China = CHN.
-Dan|||> Go with the 3; the 2 is about to be deprecated becuase we just have too
> many countries declaring themselves.
Looks pretty current to me:
http://www.iso.org/iso/en/prods-ser...n1.
html
Can you post your source that states the 2 code is going to be depreciated?
It will cause absolute confusion over here because travelling on the content
you need to have a sticker with your country code on your car and to my
knowledge Great Britian is still GB.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1148150829.923946.28160@.u72g2000cwu.googlegroups.com...
> Go with the 3; the 2 is about to be deprecated becuase we just have too
> many countries declaring themselves.
>|||Some stickers are 1,2 or 3 characters
I = Italy
F = France
NL = the Netherlands
SLO =Slovenia
I don't think those follow any ISO standards
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Makes matters worse, I wonder what standard us Europeans use then for that?
The only useful thing I found was from the US CIA site:
http://www.cia.gov/cia/publications...appendix-d.html and
there is no mention of 2 code being depreciated, to make matters even more
interesting ISO 3166 has 3 versions, 2 letter, 3 letter and 3 digit!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1148222151.073946.222330@.i40g2000cwc.googlegroups.com...
> Some stickers are 1,2 or 3 characters
> I = Italy
> F = France
> NL = the Netherlands
> SLO =Slovenia
> I don't think those follow any ISO standards
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On Sun, 21 May 2006 17:48:38 +0100, Tony Rogerson wrote:

> to make matters even more
>interesting ISO 3166 has 3 versions, 2 letter, 3 letter and 3 digit!
"The nice thing about standards is that there are so many of them"
-- Unknown
Hugo Kornelis, SQL Server MVP