Re: CHECK() Constraint on Column Using Lookup Table

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Rich Shepard
> Sent: Tuesday, May 01, 2007 10:42 AM
> To: pgsql-general@postgresql.org
> 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.