Creating Table Error

[flixer]

Hi guys,

When creating a table i get the syntax error:

“Error no. 1064

check the manual that corresponds to your mysql server version for the right syntax to use near “NULL”) at line 4.”

I am using mySQL 5.0.24a and dont know what im doing wrong. My table consists of 3 fields:

Field 1 is called “Votes” and datatype is set to “decimal”, Field 2 is called “Rating” and is “numeric” datatype and finally field 3 is called “Title” and is “varchar” datatype.

Any advice much appreciated.

[peterlaursen]

What is the SQLyog version?

Please

1) attach a screendump of the CREATE TABLE dialogue

2) copy the corresponding CREATE statement that fails from the HISTORY tab.

[peterlaursen]

Quote:

and datatype is set to “decimal”

did you specify the decimal format like decimal(19,4)?.

If you do not specify anything default is decimal(10,0)!

decimal is a 'high precision math type' in MySQL. It is good to use a decimal(x,4) for monetary data.

http://www.webyog.com/faq/29_90_en.html

[flixer]

peterlaursen wrote on Sep 23 2006, 02:33 PM:

did you specify the decimal format like decimal(19,4)?.

If you do not specify anything default is decimal(10,0)!

decimal is a 'high precision math type' in MySQL. It is good to use a decimal(x,4) for monetary data.

http://www.webyog.com/faq/29_90_en.html

Hi peter,

I'm using sqlyog v5.18, below is the history data and screenshot attached. I've also tried specifying decimal in lens column as 10,0 but i get the same error.

/*[21:37:31][ 150 ms]*/ show variables like '%character%'

/*[21:37:31][ 20 ms]*/ Set character_set_connection=latin1

/*[21:37:31][ 0 ms]*/ Set character_set_results=latin1

/*[21:37:31][ 0 ms]*/ Set character_set_client=latin1

/*[21:37:31][ 0 ms]*/ set sql_mode=''

/*[21:37:31][ 60 ms]*/ show databases

/*[21:37:33][ 0 ms]*/ use `uk-dvd`

/*[21:37:33][ 10 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[21:38:38][ 10 ms]*/ create table `uk-dvd`.`IMDBRating` ( `Votes` numeric NULL , `Rating` decimal NULL , `Title` varchar NULL )

hope you can help

regards

[peterlaursen]

the reason for the error is that you must specify the lenth of a varchar.

A numeric and a decimal is the same! Sure that you would not like an INTEGER for the 'votes' ? And you would normally want some decimals available for 'rating' ?

About numeric types in MySQL:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

I would also say that you should consider to have a Primary Key!

[flixer]

peterlaursen wrote on Sep 24 2006, 02:13 AM:

the reason for the error is that you must specify the lenth of a varchar.

A numeric and a decimal is the same! Sure that you would not like an INTEGER for the 'votes' ? And you would normally want some decimals available for 'rating' ?

About numeric types in MySQL:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

I would also say that you should consider to have a Primary Key!

Thanks again, it works now!.

[flixer]

BTW, which column would i need to set as primary key ?

[peterlaursen]

You can use any column that has unique values.

It is widely use to add a autoincrement-integer for the PK (and nothing else), but it need not be so.

[Author]

Posts