Newbie question on Create Table

I'm entirely new to mysql and am having a rather basic problem of not
being able to create a table!

The database is mysql 5.0

The statement I am using is:

CREATE TABLE `article` (
`article_key` INT NOT NULL AUTO_INCREMENT ,
`article_fkey` INT NOT NULL ,
`title` LONGTEXT( 255 ) ,
`summary` LONGTEXT( 2000 ) ,
`body` LONGTEXT( 2000 ) ,
`active` TINYINT DEFAULT '1' NOT NULL ,
`exist` TINYINT DEFAULT '0' NOT NULL ,
PRIMARY KEY ( `article_key` ) ,
INDEX ( `article_fkey` )
) TYPE = MYISAM

I'm getting the error message:
#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 '(255), `summary` MEDIUMTEXT(2000), `body` MEDIUMTEXT(2000), `ac

I just can't see what is wrong with this statement, can someone point
out the error of my ways?

Thanks

Neil

Re: Newbie question on Create Table

Neil wrote:

> I'm entirely new to mysql and am having a rather basic problem of not
> being able to create a table!
>
> The database is mysql 5.0
>
> The statement I am using is:
>
> CREATE TABLE `article` (
> `article_key` INT NOT NULL AUTO_INCREMENT ,
> `article_fkey` INT NOT NULL ,
> `title` LONGTEXT( 255 ) ,
> `summary` LONGTEXT( 2000 ) ,
> `body` LONGTEXT( 2000 ) ,
> `active` TINYINT DEFAULT '1' NOT NULL ,
> `exist` TINYINT DEFAULT '0' NOT NULL ,
> PRIMARY KEY ( `article_key` ) ,
> INDEX ( `article_fkey` )
> ) TYPE = MYISAM
>
> I'm getting the error message:
> #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 '(255), `summary` MEDIUMTEXT(2000), `body` MEDIUMTEXT(2000), `ac
>
> I just can't see what is wrong with this statement, can someone point
> out the error of my ways?
>
> Thanks
>
> Neil
>

LONGTEXT does not appear to be "sizable". Unless you are doing binary input
(BLOBS etc) you should probably be using VARCHAR(n).

See: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements. html for
data-type definitions.


Try:
CREATE TABLE article (
article_key INT NOT NULL AUTO_INCREMENT ,
Primary key (article_key),
article_fkey INT NOT NULL,
title LONGTEXT,
summary LONGTEXT ,
body LONGTEXT,
active TINYINT DEFAULT '1' NOT NULL ,
exist TINYINT DEFAULT '0' NOT NULL ,
INDEX ( article_fkey )
) TYPE = MYISAM;



--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Re: Newbie question on Create Table

"Neil" wrote in message
news:1140610397.519076.251620@f14g2000cwb.googlegroups.com.. .
> `title` LONGTEXT( 255 ) ,
> `summary` LONGTEXT( 2000 ) ,
> `body` LONGTEXT( 2000 ) ,

The LONGTEXT type doesn't take a length argument. It's a type of BLOB
field, which sizes dynamically based on the length of data you put into it.

By the way, do you realize that a single LONGTEXT field can store up to 4
gigabytes? If you need that much storage for a summary, how long is the
text it's summarizing? ;-)

VARCHAR is a string datatype that does take a length argument, using the
syntax you're using above. In MySQL 5.0.3 and later, a VARCHAR can be up to
65,532 bytes; in earlier MySQL versions, the limit is 255 bytes.

Read this page for more information:
http://dev.mysql.com/doc/refman/5.0/en/string-type-overview. html

Regards,
Bill K.

Re: Newbie question on Create Table

Many thanks Michael that has sorted it out.

I think I need to RTFM before proceeding further!

Re: Newbie question on Create Table

Thanks for the information Bill.

I will be changing the table structure as I certainly don't need
longtext on the summary.

Regards

Neil