Gary Robson wrote:

>
> ENUMs in MySQL allow you to define a set of allowed values for the field
>
> i.e. 'cat',dog',....
> the field can only contain one of the values listed above, so far you
> could use CONSTRAINTS (I think!). The twist is, when the table is
> created
> each of these values is given a numerical value as well, so 'cat'=1,
> 'dog'=2 etc
> When you use the fields in querys and functions the value returned
> depends on the context in which it is used, if you treat the field as a
> number then that is what will be returned, if you treat it as a text
> string then 'cat' or 'dog' etc will be returned. The same also applies
> to INSERTs etc

Ah, I see. The only way to do this is with a lookup table with two cols - code
and description. You join the data table with the lookup table if you want to
display the string. Typically this is usually done on the client with buffered
datasets holding the lookup tables.

>
> SETs are very similar but evaluate the allowed value as 1,2,4,8,16,32,64
> etc therefore allowing multiple values in a field. The information in
> the tables ends up being very readable, but still allows me to treat the
> fields as numeric (using a bitmask to remove a value from the field is a
> lot easier than recreating the string without that value).
>

Hmmm. This one is intriguing. Nothing obviously easy springs to mind but you
could use a variation on the previous suggestion. Essentially you have a two
col table with code values of powers of 2 and associated descriptions. The
value in the data table is then manipulated using a bit mask. For that you
will need a UDF library as Firebird doesn't have one built in.


Paul
--

Paul Reeves
http://www.ibphoenix.com
taking InterBase further