Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Fri, 08 Sep 2006 15:25:39 +0200
From: "Bernt M. Johnsen" <Bernt.Johnsen@Sun.COM>
Subject: Re: ENUM
In-reply-to: <fbb0d11d0609080552j7badccbbr9af859ad3cca8fe2@mail.gmail.com>
Sender: Bernt.Johnsen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Reply-to: Bernt.Johnsen@Sun.COM
Message-id: <45016F53.2050206@Sun.COM>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <c78e8f9e0609072233p51ffa6fcs69f6e28d2ba20cd@mail.gmail.com>
 <fbb0d11d0609080552j7badccbbr9af859ad3cca8fe2@mail.gmail.com>
User-Agent: Thunderbird 1.5.0.5 (X11/20060831)

Dan Scott wrote:
> On the SET datatype: Even the MySQL docs include a section upfront
> called "Why you shouldn't use SET"
> (http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html).
> It's not an atomic datatype. Bad.
>
> On ENUM: The SQL standard way of doing the equivalent of ENUM, and
> therefore the Derby way, is to define a check constraint on the
> column.
>
> For example, in MySQL you might do something like:
>
> CREATE TABLE nonstandard (
>  sizes ENUM('small', 'medium', 'large')
> );
>
> Implementing this as a check constraint in Derby, on the other hand,
> would look something like:
>
> CREATE TABLE standard (
>  sizes VARCHAR(32) CONSTRAINT sizes_ck CHECK (sizes IN ('small',
> 'medium', 'large'))
> );

The CHECK constraint is implemented in Derby, and is a portable
standard way of doing this. The MySQL enum is neeither standard nor
portable.

-- 
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway