From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Charles Hauser <chauser(at)duke(dot)edu>, PSQL_list <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Use of reference table ('look-up-table') and FK constraint |
Date: | 2002-10-15 17:27:51 |
Message-ID: | 200210151027.51235.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2002-10-15 18:46:03 | Re: SQL function triggers |
Previous Message | Josh Berkus | 2002-10-15 16:28:12 | Re: Messy Casts, Is there a better way? |