You have an error in your SQL syntax

[nickburrett]

Hello

Can anyone tell me what I'm doing wrong here? I'm getting the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL default '', `deposit_date` date NOT NULL default '0000-00-00', `dep' at line 31

CREATE TABLE `bookings` (
`id` int(6) NOT NULL auto_increment,
`advert_source` varchar(255) NOT NULL default '',
		`company` varchar(255) NOT NULL default '',
		`start_time` datetime NOT NULL default '0000-00-00 00:00:00',
		`finish_time` datetime NOT NULL default '0000-00-00 00:00:00',
		`event_occasion` varchar(255) NOT NULL default '',
		`brides_name` varchar(255) NOT NULL default '',
		`grooms_name` varchar(255) NOT NULL default '',
		`bg_surname` varchar(255) NOT NULL default '',
		`birthday_name` varchar(255) NOT NULL default '',
		`birthday_age` varchar(255) NOT NULL default '',
		`venue_name` varchar(255) NOT NULL default '',
		`venue_address_1` varchar(255) NOT NULL default '',
		`venue_address_2` varchar(255) NOT NULL default '',
		`venue_town` varchar(255) NOT NULL default '',
		`venue_county` varchar(255) NOT NULL default '',
		`venue_postcode` varchar(255) NOT NULL default '',
		`client_title` varchar(255) NOT NULL default '',
		`client_first` varchar(255) NOT NULL default '',
		`client_last` varchar(255) NOT NULL default '',
		`client_address_1` varchar(255) NOT NULL default '',
		`client_address_2` varchar(255) NOT NULL default '',
		`client_town` varchar(255) NOT NULL default '',
		`client_county` varchar(255) NOT NULL default '',
		`client_postcode` varchar(255) NOT NULL default '',
		`home_telephone` varchar(255) NOT NULL default '',
		`mobile_telephone` varchar(255) NOT NULL default '',
		`email_address` varchar(255) NOT NULL default '',
		`client_notes` text NOT NULL default '',
		`quotation_amount` enum NOT NULL default '',
		`deposit_date` date NOT NULL default '0000-00-00',
		`deposit_amount` enum NOT NULL default '',
		`deposit_method` varchar(255) NOT NULL default '',
		`deposit_staff` varchar(255) NOT NULL default '',
		`balance_date` date NOT NULL default '0000-00-00',
		`balance_amount` enum NOT NULL default '',
		`balance_method` varchar(255) NOT NULL default '',
		`balance_staff` varchar(255) NOT NULL default '',
		`date_booking_taken` date NOT NULL default '0000-00-00',
		`dj_requested` varchar(255) NOT NULL default '',
		`quotation_status` varchar(255) NOT NULL default '',
		PRIMARY KEY  (`id`),
UNIQUE KEY `id` (`id`)
)
ENGINE=MyISAM AUTO_INCREMENT=1;

[Old Pedant]

Well, for starters, none of your lines that use enum are legal.

You *MUST* give the possible values of the enum right there in the declaration. Further, you can't use a default of ''. Period.

mysql> create table foo ( xyz enum not null default '' );
ERROR 1064 (42000): You have an error in your SQL syntax...

mysql> create table foo ( xyz enum('a','b','c') not null default '' );
ERROR 1067 (42000): Invalid default value for 'xyz'

mysql> create table foo ( xyz enum('a','b','c') not null default 'a' );
Query OK

See it?

And I think, then, your date lines will then show up okay:

mysql> create table foo ( xyz date not null default '0000-00-00');
Query OK

That is, I think the real error is in your enum lines, not your date lines.

Oh...and a PRIMARY KEY is *ALREADY* a UNIQUE KEY, so your UNIQUE KEY line there is doing nothing but cause overhead you don't need.

[guelphdad]

I always wonder why people create columns that are NOT NULL and then assign an empty string as the default value. Why bother?

[Old Pedant]

Because then they don't have to handle the case of a NULL value when they SELECT data. That is, they don't have to check in PHP/ASP/JSP if the DB value is null and convert it to a blank string.

Of course, you CAN also do that in the SELECT:

SELECT IFNULL(field,'') AS field ...

BUt if you know the value can't be null you don't need special handling in either the SELECT or the client language.

Personally, I prefer handling the NULLs. But I do understand why some people don't want to.

[nickburrett]

Originally Posted by guelphdad

I always wonder why people create columns that are NOT NULL and then assign an empty string as the default value. Why bother?

Because people like me haven't got a clue what they are doing!

The code I pasted was generated from a script I came across here

[Old Pedant]

Well, clearly the claim that All major bugs now fixed! is not true.

I'd go so far as to say that their table generation is a joke and should NOT be depended upon.

[manioxy]

The double quote should not be part of the SQL query. SQL also does not understand PHP variables.

[Old Pedant]

Originally Posted by manioxy

The double quote should not be part of the SQL query. SQL also does not understand PHP variables.

And what in the world does that comment have to do with ANYTHING in this thread?