|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Referential Integrity - Countries & States/Provinceswith 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! -Dan 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.
Show quote
"Dan Manes" <danth***@cox.net> wrote in message Doesn't fail for me: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) > ) > 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 Looks pretty current to me: > many countries declaring themselves. http://www.iso.org/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.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. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "--CELKO--" <jcelko***@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/factbook/appendix/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! -- Show quoteTony 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.g***@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 "The nice thing about standards is that there are so many of them">interesting ISO 3166 has 3 versions, 2 letter, 3 letter and 3 digit! -- Unknown -- Hugo Kornelis, SQL Server MVP Actually, it seems they have their own standard entirely, as set by the UN.
http://www.unece.org/trans/conventn/Distsigns.pdf Show quote "SQL Menace" <denis.g***@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/ > It was an email from a friend who is still working with Standards. The
auto stickers are not on ISO codes anyway. I do not know who sets those abbreviation codes. --CELKO-- wrote:
> It was an email from a friend who is still working with Standards. The The United Nations? Google for> auto stickers are not on ISO codes anyway. I do not know who sets > those abbreviation codes. "Distinguishing Signs of Vehicles in International Traffic" Jamie. --  |
|||||||||||||||||||||||
Other interesting topics