Re: Use of reference table ('look-up-table') and FK constraint

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