> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Rich Shepard > Sent: Tuesday, May 01, 2007 10:42 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: [GENERAL] CHECK() Constraint on Column Using Lookup Table > > I've seen the syntax for using a lookup table in a CHECK() constraint, > but > I cannot find a reference to it. It's not in Section 5.3.1 of the 8.2 > docs. > > Specifically, I want to reference a table of ISO 2-letter codes for US > states and Canadian provinces/territories in a table with name and address > columns. It sounds like a foreign key to me, and not a check constraint. If the state/province/territory data is in a table, then use a foreign key. It is likely that you will need to add things in the future (e.g. places like Virgin Islands, international expansion into Mexico, Europe, etc.) It is better to be data driven than to use a hardwired list in the SQL definition. You will need some way to handle APO and FPO addresses (which are a bit different). Maybe something along the lines of this: CREATE TABLE localhost.dbo.state_prov_terr (abbr CHAR(4) PRIMARY KEY, name VARCHAR(255)); INSERT INTO state_prov_terr (abbr, name) values ('AB', 'Alberta'); INSERT INTO state_prov_terr (abbr, name) values ('AK', 'Alaska'); INSERT INTO state_prov_terr (abbr, name) values ('AL', 'Alabama'); INSERT INTO state_prov_terr (abbr, name) values ('APO', '(Army or Air Force Post Office)'); INSERT INTO state_prov_terr (abbr, name) values ('AR', 'Arkansas'); INSERT INTO state_prov_terr (abbr, name) values ('AS', 'American Samoa'); INSERT INTO state_prov_terr (abbr, name) values ('AZ', 'Arizona'); INSERT INTO state_prov_terr (abbr, name) values ('BC', 'British Columbia'); INSERT INTO state_prov_terr (abbr, name) values ('CA', 'California'); INSERT INTO state_prov_terr (abbr, name) values ('CO', 'Colorado'); INSERT INTO state_prov_terr (abbr, name) values ('CT', 'Connecticut'); INSERT INTO state_prov_terr (abbr, name) values ('DC', 'District of Columbia'); INSERT INTO state_prov_terr (abbr, name) values ('DE', 'Delaware'); INSERT INTO state_prov_terr (abbr, name) values ('FL', 'Florida'); INSERT INTO state_prov_terr (abbr, name) values ('FM', 'Federated States of Micronesia'); INSERT INTO state_prov_terr (abbr, name) values ('FPO', '(Fleet Post Office for the Navy, Marine Corps, or Coast Guard)'); INSERT INTO state_prov_terr (abbr, name) values ('GA', 'Georgia'); INSERT INTO state_prov_terr (abbr, name) values ('GU', 'Guam'); INSERT INTO state_prov_terr (abbr, name) values ('HI', 'Hawaii'); INSERT INTO state_prov_terr (abbr, name) values ('IA', 'Iowa'); INSERT INTO state_prov_terr (abbr, name) values ('ID', 'Idaho'); INSERT INTO state_prov_terr (abbr, name) values ('IL', 'Illinois'); INSERT INTO state_prov_terr (abbr, name) values ('IN', 'Indiana'); INSERT INTO state_prov_terr (abbr, name) values ('KS', 'Kansas'); INSERT INTO state_prov_terr (abbr, name) values ('KY', 'Kentucky'); INSERT INTO state_prov_terr (abbr, name) values ('LA', 'Louisiana'); INSERT INTO state_prov_terr (abbr, name) values ('MA', 'Massachusetts'); INSERT INTO state_prov_terr (abbr, name) values ('MB', 'Manitoba'); INSERT INTO state_prov_terr (abbr, name) values ('MD', 'Maryland'); INSERT INTO state_prov_terr (abbr, name) values ('ME', 'Maine'); INSERT INTO state_prov_terr (abbr, name) values ('MH', 'Marshall Islands'); INSERT INTO state_prov_terr (abbr, name) values ('MI', 'Michigan'); INSERT INTO state_prov_terr (abbr, name) values ('MN', 'Minnesota'); INSERT INTO state_prov_terr (abbr, name) values ('MO', 'Missouri'); INSERT INTO state_prov_terr (abbr, name) values ('MP', 'Northern Mariana Islands'); INSERT INTO state_prov_terr (abbr, name) values ('MS', 'Mississippi'); INSERT INTO state_prov_terr (abbr, name) values ('MT', 'Montana'); INSERT INTO state_prov_terr (abbr, name) values ('NB', 'New Brunswick'); INSERT INTO state_prov_terr (abbr, name) values ('NC', 'North Carolina'); INSERT INTO state_prov_terr (abbr, name) values ('ND', 'North Dakota'); INSERT INTO state_prov_terr (abbr, name) values ('NE', 'Nebraska'); INSERT INTO state_prov_terr (abbr, name) values ('NH', 'New Hampshire'); INSERT INTO state_prov_terr (abbr, name) values ('NJ', 'New Jersey'); INSERT INTO state_prov_terr (abbr, name) values ('NL', 'Newfoundland and Labrador'); INSERT INTO state_prov_terr (abbr, name) values ('NM', 'New Mexico'); INSERT INTO state_prov_terr (abbr, name) values ('NS', 'Nova Scotia'); INSERT INTO state_prov_terr (abbr, name) values ('NT', 'Northwest Territories'); INSERT INTO state_prov_terr (abbr, name) values ('NU', 'Nunavut'); INSERT INTO state_prov_terr (abbr, name) values ('NV', 'Nevada'); INSERT INTO state_prov_terr (abbr, name) values ('NY', 'New York'); INSERT INTO state_prov_terr (abbr, name) values ('OH', 'Ohio'); INSERT INTO state_prov_terr (abbr, name) values ('OK', 'Oklahoma'); INSERT INTO state_prov_terr (abbr, name) values ('ON', 'Ontario'); INSERT INTO state_prov_terr (abbr, name) values ('OR', 'Oregon'); INSERT INTO state_prov_terr (abbr, name) values ('PA', 'Pennsylvania'); INSERT INTO state_prov_terr (abbr, name) values ('PE', 'Prince Edward Island'); INSERT INTO state_prov_terr (abbr, name) values ('PR', 'Puerto Rico'); INSERT INTO state_prov_terr (abbr, name) values ('PW', 'Palau'); INSERT INTO state_prov_terr (abbr, name) values ('QC', 'Quebec'); INSERT INTO state_prov_terr (abbr, name) values ('RI', 'Rhode Island'); INSERT INTO state_prov_terr (abbr, name) values ('SC', 'South Carolina'); INSERT INTO state_prov_terr (abbr, name) values ('SD', 'South Dakota'); INSERT INTO state_prov_terr (abbr, name) values ('SK', 'Saskatchewan'); INSERT INTO state_prov_terr (abbr, name) values ('TN', 'Tennessee'); INSERT INTO state_prov_terr (abbr, name) values ('TX', 'Texas'); INSERT INTO state_prov_terr (abbr, name) values ('UT', 'Utah'); INSERT INTO state_prov_terr (abbr, name) values ('VA', 'Virginia'); INSERT INTO state_prov_terr (abbr, name) values ('VI', 'Virgin Islands'); INSERT INTO state_prov_terr (abbr, name) values ('VT', 'Vermont'); INSERT INTO state_prov_terr (abbr, name) values ('WA', 'Washington'); INSERT INTO state_prov_terr (abbr, name) values ('WI', 'Wisconsin'); INSERT INTO state_prov_terr (abbr, name) values ('WV', 'West Virginia'); INSERT INTO state_prov_terr (abbr, name) values ('WY', 'Wyoming'); INSERT INTO state_prov_terr (abbr, name) values ('YT', 'Yukon'); And then add a foreign key.
