MySQL Syntax Error?

taylor_venable · 06-09-2007, 11:54 AM

I'm trying to create a table in MySQL 5.0 which will hold data on words of a certain part of speech in the English language. So, for example, according to the controlled English system I'm working with, adjectives have properties like the positive, comparative, and superlative forms (e.g. "good", "better", "best"). You can also specify aliases for certain properties, which are alternatives that have the same meaning. The aliases are lists, so there could be some as-yet-unknown number of them. As such, I tried to create a table for adjectives (where each row is a word entry) using the following SQL:

Code:

CREATE TABLE `capture`.`adjective` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `positive` VARCHAR NOT NULL,
  `comparative` VARCHAR NOT NULL,
  `superlative` VARCHAR NOT NULL,
  `positive_aliases` SET,
  `comparative_aliases` SET,
  `superlative_aliases` SET,
  `complementing_preposition` VARCHAR NOT NULL,
  `comment` VARCHAR,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
AUTO_INCREMENT = 0
CHARACTER SET utf8 COLLATE utf8_general_ci;

This was generated by using one of the MySQL GUI tools. But I get a syntax error (#1064) when I try to execute it:

Code:

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,
  `comparative` VARCHAR NOT NULL,
  `superlative` VARCHAR NOT NULL,
' at line 3

I've checked the generated SQL against what I understand from "SQL in a Nutshell" and it seems to check out OK by my human eye. But the server thinks there's something wrong, obviously. The full ID of the server's version is "MySQL 5.0.38-Ubuntu_0ubuntu1-log" if that has any meaning. Thanks for any ideas!

Guttorm · 06-10-2007, 03:28 AM

Hi

Varchar needs maximum length, so you need to change "varchar" into for example "varchar (255)". Also, "set" by itself is not a type. You need to specify some values it can have. And I think you might want to not use a set for this:

positive_aliases SET ('good','better','best')
means that alias can have a combination of any of those values.

positive_aliases ENUM ('good','better','best')
means it will have one of those values.

Or maybe just use varchar(255) for that one too, if you don't know all the alternatives yet.

See:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

Also, what GUI tool did you use? I use phpmyadmin. With it you can do a lot of stuff like creating tables, and you can look at the SQL if you want to, but you really don't need to. The only thing better than phpmyadmin I found is "navicat" but it's commercial software. They have an evaluation for it if you want to try it, just google for it.

Hope this helps.

taylor_venable · 06-10-2007, 08:39 AM

Quote:

Originally Posted by Guttorm

Varchar needs maximum length, so you need to change "varchar" into for example "varchar (255)". Also, "set" by itself is not a type. You need to specify some values it can have. And I think you might want to not use a set for this:

positive_aliases SET ('good','better','best')
means that alias can have a combination of any of those values.

positive_aliases ENUM ('good','better','best')
means it will have one of those values.

Or maybe just use varchar(255) for that one too, if you don't know all the alternatives yet.

Thanks, I didn't know that you had to specify those things. For the non-aliases, it's perfectly alright. But that is unfortunate about the set thing, because it's impossible to know what values the user will provide for aliases. I may end up and use the TEXT type instead, and delimit the aliases with newlines. (The actual usable form of the information is a Prolog database, but we want to store it on a server somewhere with a web interface, so something like MySQL seemed like a logical choice. The two systems are not directly analogous.

)

Quote:

Originally Posted by Guttorm

Also, what GUI tool did you use? I use phpmyadmin. With it you can do a lot of stuff like creating tables, and you can look at the SQL if you want to, but you really don't need to. The only thing better than phpmyadmin I found is "navicat" but it's commercial software. They have an evaluation for it if you want to try it, just google for it.

I use phpmyadmin sometimes, but most of the time I use MySQL Administrator and MySQL Query Browser (both available as free software from mysql.org). They're both pretty nice tools, and in my experience offer a bit more functionality than phpmyadmin.

Quote:

Originally Posted by Guttorm

Hope this helps.

It sure does. Thanks!