Re: What is the good equivalent for ENUM ?

On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
> >>>>> "SD" == Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:
>
> SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:
> >> The problem is that this MySQL database uses ENUM, do you see what can I
> >> do to migrate ENUM into PostgreSQL ?
>
> SD> varchar with check constraints. Add constraits to allow only
> SD> certain values of varchar string.
>
> I used to do this. It turns out to be horribly inflexible when you
> need to alter the enum values since the constraints cannot easily be
> changed.

It'll be better when domains have alterable constraints. Your
way is the traditional (and best, IMO) way, though.

> What I do is create a short table for the enum like this:
>
> CREATE TABLE status_levels (
> status varchar(10) PRIMARY KEY
> ) WITHOUT OIDS;
> INSERT INTO status_levels (status) VALUES ('active');
> INSERT INTO status_levels (status) VALUES ('overdue');
> INSERT INTO status_levels (status) VALUES ('suspended');
> INSERT INTO status_levels (status) VALUES ('terminated');
>
> then reference it via foreign key from the "enum" field:
>
> CREATE TABLE whatever (
> ...
> status varchar(10) NOT NULL DEFAULT 'active' REFERENCES status_levels(status),
> ...
> );
>

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

"You ask us the same question every day, and we give you the
same answer every day. Someday, we hope that you will believe us..."
U.S. Secretary of Defense Donald Rumsfeld, to a reporter