Adding enum fields

Anon

I'm working on a forum with categories, I'm putting the categories in an enum field.

For easier category creation, is there a way to add a new enum field without having to put every existing field?

[deleted]

ENUM fields are evil (and useless)

Be aware that you may NOT change the order of the items in the ENUM definition, or all your data will be trashed.

Anon

I mean adding onto, at the end. enums are perfect for what I need to do here and are currently working so well that I know I will keep using them.

[deleted]

You just keep using ENUMs. 🙂

I'll hear from you again when the times comes to remove an option from the ENUM set, or when it's time to port to a real database.

saloon12yrd

Hi vincent,

this makes me curious, do SET fields have the same disadvantages? So far they (SET) have worked fine for me (on MySQL), never tried ENUM though.

Greets,

Dominique

[deleted]

SET is just a multi-value version of ENUM.
Both fields can hold only those values that you defined in the table definition, but SET can hold several of those values, where ENUM can hold only one.

But the problem is that MySQL's implementations of SET and ENUM types are... well, bad.
As always, MySQL can't be bothered to do any kind of integrety checks, and changing the ENUM and SET definition can easily destroy your data.

Try building a column of the ENUM type:

CREATE TABLE bla (a ENUM('foo','bar'));

and insert some data
INSERT INTO bla (a) values ('foo');
INSERT INTO bla (a) values ('bar');

SELECT * FROM bla;

All is well.

Now add new option:
ALTER TABLE bla change a a ENUM('foo','bar','poo');
SELECT * FROM bla;

All is still ok.

You can even change the order around (a spectacular feat of the MySQL boys):

ALTER TABLE bla change a a ENUM('poo','foo','bar');
SELECT * FROM bla;

But change one of them, and poof, you data is corrupted, gone, dead.

ALTER TABLE bla change a a ENUM('poo','foo','barf');
SELECT * FROM bla;

Note that MySQL does this WITHOUT WARNING!!

The only way to do this 'safely' is to add a new option to the ENUM type, use an UPDATE to change all the old values to the new values, and then remove the old option from the ENUM:

CREATE TABLE bla (a ENUM('foo','bar','poo'));
INSERT INTO bla (a) values ('foo');
INSERT INTO bla (a) values ('bar');
INSERT INTO bla (a) values ('poo');
SELECT FROM bla;
ALTER TABLE bla change a a ENUM('foo','bar','poo','barf');
UPDATE bla SET a = 'barf' WHERE a='bar';
SELECT FROM bla;
ALTER TABLE bla change a a ENUM('foo','poo','barf');
SELECT * FROM bla;

If you did this the 'proper' way, with a referencial constraint or a one-to-many relationship, you would always get a warning, the database will always refuse to do it, and your data would never get corrupted.

saloon12yrd

This reads like one of the flamewar causing "educational features" of MySQL 😉
I'll definately try out your example, sounds like a great fun.
In fact this reminds me of referencing to a name instead of an id in a "newbie" program.

Does this crash the whole DB or just the table? Is it repairable? Oh I'll find out for myself this evening 🙂

Greets,

Dominique

Anon

I think this is what you'll find when you follow Vincent's example:

Assuming the ENUM values were ('poo','foo','bar') and are now ('poo','foo','barf'):
All the records that used 'poo' and 'foo' should still be okay. But all the records that formerly had 'bar' will now have blank fields.

[deleted]

Indeed. you may still be able to fix that with a 'simple' update, but it you have changed two options, it's time to grab the backup-tapes.

[deleted]

"This reads like one of the flamewar causing "educational features" of MySQL 😉"

I intend to put MySQL on the grill a lot in future :-)

saloon12yrd

Hehe then you'd better hurry, the MySQL folx seem to change a lot in 4.1:
- foreign keys (yeehaw)
- subselects (nice for those in need, join does it for me)
- stored procedures (yeehaw)

Let's hope this happens. If it ain't I really need to check out PostgreSQL

Or to quote mysql.com:
"After those additions, critics of MySQL have to be more imaginative than ever in pointing out deficiencies in the MySQL Database Management System. For long already known for its stability, speed, and ease of use, MySQL will then match the requirement checklist of very demanding buyers."

We'll see whether they redefine atomicity to what it really is, hehe.

Dominique

[deleted]

lol

MySQL's answer to everything: "it will be done in future releases"

So far they only got row-level-locking and transactions, and 4.0 is still in the alpha stage.

BTW:
"- subselects (nice for those in need, join does it for me)"

You've been reading too many MySQL docs, only the only subqueries you can rewrite as a JOIN are those that shouldn't have been written as a subquery in the first place :-)

saloon12yrd

true true, "future release" is a keyword.
Regarding too many docs... nah, just one: split_manual.tar.gz 😉
The thing is that I don't need subqueries, no matter what I do. Maybe I'm not on the hard edge of SQL coding but
SELECT a.id FROM asd AS a, qwert AS q, [...] WHERE [...]
does the trick for me. I haven't realized and disadvantage or slowdown. Hmm okay that was not a join but you get the idea...

Dominique

BTW, where in .nl do you live? Is it far from aachen (.de)? 😉

[deleted]

That's the trouble with MySQL.
Many people read about it, install it (which so simple a donkey could do it) and it works,
they can run simple queries.

And for most people, that's where their adventure into SQL ends, they just use what is available in MySQL, thinking that "that's it", not realizing there are so many more usefull functions that make MySQL's subset look like.. well, like a subset 🙂

Anyway, enough OT for now, if you guys need more, mail me :-)

saloon12yrd

Whoah, confirmed with ENUM and SET.
Man this is bad, even poorly designed.

Let me guess, Postgre does this correctly? Maybe it's time to check that thing out. God praise abstraction layers.

Dominique

smarlowe

Really, what MySQL is trying to accomplish is the same as a foreign key here. Enum is pretty easy to implement, but like vincent said, they can be downright dangerous.

Now, a real database will do the same thing wiht a little more work up front on your part, and ensure your data doesn't get scramble by you accidentally deleting or editing the wrong field in your foreign key.

Let me give you an example using Postgresql...

create table enum (fieldname text, id serial);

This will create the table that will hold our "enum" data that we would have put in the enum column in mysql.

Let's insert some data to get started.

insert into enum (fieldname) values ('big');
insert into enum (fieldname) values ('med');
insert into enum (fieldname) values ('sml');
so we have big, medium, and small for our enum types.

Next, we create a table that has a foreign key pointing to the enum table.

create table folks (fname text, lname text, id serial, enum_id int constraint enum_check references enum (id));

This creates a table that uses enum to decide which values are "legal" for inserting into the enum_id column.

Now we can insert data into our database like so:
insert into folks (fname, lname, enum_id) values ('Scott','Marlowe',(select id from enum where fieldname='big'));

Notice I used a subselect to decide which id to grab. I.e. I don't need to know the actual number, I let a subselect do it for me.

Now for the interesting part...

delete from enum where fieldname='big';

Yields the following error:

enum_check referential integrity violation - key in enum still referenced from folks

But, this works:
update enum set fieldname='huge' where fieldname='big'

so now we can create a view, that looks at the two tables like one:

create view bigview as (select fname, lname, fieldname as size from folks join enum on (folks.enum_id=enum.id));

Now we can get the data with:

select * from bigview;
fname | lname | size
-------+---------+------
Scott | Marlowe | huge

Enough for now... I gotta get some work done... :-)

saloon12yrd

Smooth example.
Gonna check that Postgre thing out when I got the time.

Dominique

smarlowe

Postgresql does rock. All the docs I needed to do that was from this one line on the psql monitor:

\h create table
Command: CREATE TABLE
Description: Creates a new table
Syntax:
CREATE [ TEMPORARY | TEMP ] TABLE table_name (
{ column_name type [ column_constraint [ ... ] ]
| table_constraint } [, ... ]
) [ INHERITS ( parent_table [, ... ] ) ]

where column_constraint can be:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT value | CHECK (condition) |
REFERENCES table [ ( column ) ] [ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE action ] [ ON UPDATE action ]
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}

and table_constraint can be:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( condition ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES table [ ( column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ]
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}

The online help in psql rules...

[deleted]

lol

Am I nolonger alone in my fight for PostgreSQL? :-)

[deleted]

PostgreSQL doesn't have an ENUM type.
But you already knew that from Scott's brilliant explenation that I am going to shamelessly copy into YAPF :-)