Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Charles Hauser <chauser@duke.edu>,
	PSQL_list <pgsql-sql@postgresql.org>
Subject: Re: Use of reference table ('look-up-table') and FK constraint
Date: Tue, 15 Oct 2002 10:27:51 -0700
References: <1034788500.414.63.camel@pandorina.biology.duke.edu>
In-Reply-To: <1034788500.414.63.camel@pandorina.biology.duke.edu>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210151027.51235.josh@agliodbs.com>


Charles,

CREATE TABLE phone_types (
	type VARCHAR(10) NOT NULL PRIMARY KEY
);

INSERT INTO phone_types
VALUE ( 'Work' );
INSERT INTO phone_types
VALUE ( 'Home' );
etc ...

CREATE TABLE phone (
         phone_id             Identifier_type IDENTITY,
         phone_number         varchar(20) NOT NULL,
         community_id         numeric(12) NOT NULL,
         type                 varchar(10) NULL REFERENCES phone_types(type)=
 ON=20
DELETE SET NULL
  );

Simple, neh?   And it lets you add new types, at any time, without re-build=
ing=20
the table.   IMHO, the guy who built the database you're converting was=20
either a total hack or working around some serious platform shortcomings.

BTW, are you sure you want phone.type to be NULLable?   Were it me, I would=
=20
make it required, in which case the declaration would be:

         type                 varchar(10) NOT NULL REFERENCES=20
phone_types(type)

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco