Enum default values

[Richard Dyce]

Hi,

 I'm having problems with setting default values for an enum. It's for 
a booking system, and I'm keeping track of which day a property starts 
a booking period. It's useful to have the enum in day order therefore. 
Omitting the prompts, here's what's happening;

*****

drop table property;

create table property (
	property_id int(10) unsigned NOT NULL auto_increment,
	name char(30) default NULL,
	changeday enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') not null 
default 'Sat',
	PRIMARY KEY  (property_id)
);

insert into property values ('','Book-keeper\'s Cottage','Thu');
insert into property values ('','Inglenook Barn','Fri');
insert into property values ('','Maggie\'s House','Fri');
insert into property values ('','Riverside View','');
insert into property values ('','The Manse','');
insert into property values ('','Heathside','');
select * from property;
+-------------+-----------------------+-----------+
| property_id | name                  | changeday |
+-------------+-----------------------+-----------+
|           1 | Book-keeper's Cottage | Thu       |
|           2 | Inglenook Barn        | Fri       |
|           3 | Maggie's House        | Fri       |
|           4 | Riverside View        |           |
|           5 | The Manse             |           |
|           6 | Heathside             |           |
+-------------+-----------------------+-----------+
6 rows in set (0.00 sec)
****

and...

****

show create table property\G
*************************** 1. row ***************************
       Table: property
Create Table: CREATE TABLE `property` (
  `property_id` int(10) unsigned NOT NULL auto_increment,
  `name` char(30) default NULL,
  `changeday` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') NOT NULL 
default 'Sat',
  PRIMARY KEY  (`property_id`)
) TYPE=MyISAM
1 row in set (0.01 sec)

****

shows that what I think is a legal definition for the table is what's 
being recorded.

So no luck with default value being specified, i.e. Sat is not showing 
up in the changeday column for the last three properties. I've tried 
changing the enum defn statement to
	changeday enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') not null,
and
	changeday enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') default 
'Sat' not null, // which I'm sure is syntactically wrong anyway
just to see if I could get Sunday to show up, and no luck either. What 
am I missing?

I'm running 4.0.16-standard server, under OS X 10.3.1, via the terminal.

Any help gratefully received!

rgrds
R
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]