>> If I try to INSERT an illegel value for a SET or
>> ENUM field, MySQL will return an error message,
>> which is what you want it to do.
Frank> Have you looked at or tried the CHECK
Frank> constraint? See the SQL code for CREATE TABLE
Frank> ...
Frank> If you need something more flexible, you can
Frank> use an auxiliary table to store the "enum"
Frank> values, and use a foreign key constraint.
Thank you to everyone who has responded telling me
about the CHECK constraint. I had that figured out late
last night as the solution to substitute for ENUM.
No one who has responded (four people so far) has yet
quite hit the nail on the head regarding a substitute
for MySQL's SET data type. To save myself a lot of
retyping, I'll explain to the list:
Whereas an ENUM may take exactly one of a set of
values, a SET may have zero or more. MySQL displays
them as a comma-separated list. Therefore, if I have a
column abc SET('one','two','three'), acceptable values
of abc are '', 'one', 'two', 'three', 'one,two'
'one,three', 'two,three', and 'one,two,three'.
It's like the difference on a Web form with one field
that uses mutually exclusive radio buttons and another
that uses multiple select buttons. I've used these
often in backend scripts to process MySQL + Perl + CGI
+ DBI + Apache based forms.
(The project at hand has nothing to do with the Web.)
Consider as an example a team where you have a staff of
four members any of whom may be assigned to a given
project. team might equal 'john' or 'joe,judy', or
'john,joe,phil', etc. That's exactly the form I'm used
to seeing these things get passed in from a Web form to
Perl.
This is by no means an obscure thing. In MySQL it's as
simple as can be. Surely there is a straightforward way
to deal with it in PostgreSQL?
Abundant thanks again to those willing to share their
experience.
--
Lynn David Newton
Phoenix, AZ