You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Chee Cheng <ch...@gmail.com> on 2006/09/08 07:33:04 UTC
ENUM
Hi,
Does Derby offer enumeration type like ENUM and SET? If not, what's the
best way to restrict a column to certain pre-defined value?
Thank you.
Re: ENUM
Posted by Chee Cheng <ch...@gmail.com>.
Hi Dan, Bernt, and Rick,
Thank you very much for your answers. I understand the correct way to do "enum"
now. Thank you.
Re: ENUM
Posted by Rick Hillegas <Ri...@Sun.COM>.
As a further refinement on this excellent approach, you can encapsulate
the validator in a function. Something like this:
CREATE TABLE standard (
sizes VARCHAR(32) CONSTRAINT sizes_ck CHECK ( sizeIsLegal( sizes ) )
);
For more information on how to wire Java methods into your DDL and
queries, please see the Derby Reference Manual section on "CREATE FUNCTION".
Hope this helps,
-Rick
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'))
> );
>
> Pretty simple.
>
> Dan
>
> On 08/09/06, Chee Cheng <ch...@gmail.com> wrote:
>
>> Hi,
>>
>> Does Derby offer enumeration type like ENUM and SET? If not, what's the
>> best way to restrict a column to certain pre-defined value?
>>
>> Thank you.
>>
>>
Re: ENUM
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
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
Re: ENUM
Posted by Dan Scott <de...@gmail.com>.
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'))
);
Pretty simple.
Dan
On 08/09/06, Chee Cheng <ch...@gmail.com> wrote:
> Hi,
>
> Does Derby offer enumeration type like ENUM and SET? If not, what's the
> best way to restrict a column to certain pre-defined value?
>
> Thank you.
>
>