From: Josh Berkus <josh@agliodbs.com>
To: Charles Hauser <chauser@duke.edu>
To: 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
Message-ID: <200210151027.51235.josh@agliodbs.com> (raw)
In-Reply-To: <1034788500.414.63.camel@pandorina.biology.duke.edu>
References: <1034788500.414.63.camel@pandorina.biology.duke.edu>
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
DELETE SET NULL
);
Simple, neh? And it lets you add new types, at any time, without re-building
the table. IMHO, the guy who built the database you're converting was
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
make it required, in which case the declaration would be:
type varchar(10) NOT NULL REFERENCES
phone_types(type)
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, chauser@duke.edu
Subject: Re: Use of reference table ('look-up-table') and FK constraint
In-Reply-To: <200210151027.51235.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox