What is wrong with this??

[manking]

Using mysql 5 what is wrong with this statement?

DROP TABLE IF EXISTS messages_ulti;
CREATE TABLE messages_ulti(
     PRIMARY KEY (ID),
     ID INT(11) AUTO_INCREMENT,
     fromID VARCHAR(50) DEFAULT '',
     toID VARCHAR(50) DEFAULT '',
     Subject VARCHAR(50) DEFAULT "Welcomemessage from Webmaster!",
     Message LONGBLOB ,
     read SMALLINT DEFAULT '',
     INDEX fromID (fromID ASC),
     INDEX ID (ID ASC),
     INDEX toID (toID ASC)     ) ;

 

[max1x]

PRIMARY KEY (ID),
ID INT(11) AUTO_INCREMENT,

It might be just me, but I've don't create table like above, rather like so.

ID INT(11) PRIMARY KEY AUTO_INCREMENT,

 

[guelphdad]

The word READ is a reserved word so you should change the column name to something else.

 

[lgarner]

A. "read" is a MySQL keyword. Either change it or enclose it in backticks.
B. '' is an invalid default value for a SMALLINT. Use 0.

This works:

DROP TABLE IF EXISTS messages_ulti;
CREATE TABLE messages_ulti(
PRIMARY KEY (ID),
ID INT(11) AUTO_INCREMENT,
fromID VARCHAR(50) DEFAULT '',
toID VARCHAR(50) DEFAULT '',
Subject VARCHAR(50) DEFAULT "Welcomemessage from Webmaster!",
Message LONGBLOB ,
`read` SMALLINT DEFAULT 0,
INDEX fromID (fromID ASC),
INDEX ID (ID ASC),
INDEX toID (toID ASC) ) ;