Re: create table problems??

[Paul DuBois]

At 19:09 -0400 4/19/04, Andy B wrote:
hi..

i am trying to create this table:
CREATE TABLE rnjresort_events (
  Id int(25) NOT NULL auto_increment,
  Type enum('Annual','OneTime') binary NOT NULL default 'Annual',
  StartDate varchar(200) binary NOT NULL default '',
  EndDate varchar(200) binary NOT NULL default '',
  Name varchar(100) binary NOT NULL default '',
  County varchar(50) binary NOT NULL default '',
  Notes varchar(255) binary NOT NULL default '',
  StartingDay varchar(30) NOT NULL default '',
  StartingMonth int(2) default NULL,
  StartingYear year(4) default NULL,
  EndingDay varchar(30) NOT NULL default '',
  EndingMonth int(2) default NULL,
  EndingYear year(4) default NULL,
  PRIMARY KEY  (Id)
) TYPE=MyISAM CHARSET=latin1;
it was created on a test server (mysql 4.1-alpha) and needs to be created on
the real server (mysql 4.0.18)...
i try and do a \. events2.sql to run the script its in to create it and all
i end up getting on the real server is this:
error 1064: syntax error. check the manual for the correct syntax to use for
your version of mysql near default 'Annual',   StartDate varchar(200) binary
NOT NULL default '',
  EndDate varchar(200) bina
any ideas what could be the problem with this? the table unfortunately needs
to be created exactly as it is shown above...
Why is that?

If that's really true, then you cannot do it.  ENUM does not support
BINARY in 4.0.  (In addition, the CHARSET table option at the end is 
not supported, though that will just be ignored.)

If you have to create the table *exactly* as shown above, you're requiring
backward compatibility that does not exist.  If you can make some changes,
then declare the table like this:
CREATE TABLE rnjresort_events (
  Id int(25) NOT NULL auto_increment,
  Type enum('Annual','OneTime') NOT NULL default 'Annual',
  StartDate varchar(200) binary NOT NULL default '',
  EndDate varchar(200) binary NOT NULL default '',
  Name varchar(100) binary NOT NULL default '',
  County varchar(50) binary NOT NULL default '',
  Notes varchar(255) binary NOT NULL default '',
  StartingDay varchar(30) NOT NULL default '',
  StartingMonth int(2) default NULL,
  StartingYear year(4) default NULL,
  EndingDay varchar(30) NOT NULL default '',
  EndingMonth int(2) default NULL,
  EndingYear year(4) default NULL,
  PRIMARY KEY  (Id)
) TYPE=MyISAM;

 i wondering if the "default
''" sections of the column defines are giving it the problem...shrug


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]