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 BerkusAglio Database SolutionsSan Francisco