What is wrong with this create table statement

Question

Does anyone have any idea what is wrong with this create statement for mysql?

EDIT: now it states the error is near: revised VARCHAR(20), paypal_accept TINYINT, pre_terminat' at line 4

Thanks for the help everyone

Still errors after using sql beautifier though

CREATE TABLE AUCTIONS (
  ARTICLE_NO      VARCHAR(20),
  ARTICLE_NAME    VARCHAR(100),
  SUBTITLE        VARCHAR(20),
  CURRENT_BID     VARCHAR(20),
  START_PRICE     VARCHAR(20),
  BID_COUNT       VARCHAR(20),
  QUANT_TOTAL     VARCHAR(20),
  QUANT_SOLD      VARCHAR(20),
  START           DATETIME,
  ENDS            DATETIME,
  ORIGIN_END      DATETIME,
  SELLER_ID       VARCHAR(20),
  BEST_BIDDER_ID  VARCHAR(20),
  FINISHED        VARCHAR(20),
  WATCH           VARCHAR(20),
  BUYITNOW_PRICE  VARCHAR(20),
  PIC_URL         VARCHAR(20),
  PRIVATE_AUCTION VARCHAR(20),
  AUCTION_TYPE    VARCHAR(20),
  INSERT_DATE     DATETIME,
  UPDATE_DATE     DATETIME,
  CAT_1_ID        VARCHAR(20),
  CAT_2_ID        VARCHAR(20),
  ARTICLE_DESC    VARCHAR(20),
  DESC_TEXTONLY   VARCHAR(20),
  COUNTRYCODE     VARCHAR(20),
  LOCATION        VARCHAR(20),
  CONDITION       VARCHAR(20),
  REVISED         VARCHAR(20),
  PAYPAL_ACCEPT   TINYINT,
  PRE_TERMINATED  VARCHAR(20),
  SHIPPING_TO     VARCHAR(20),
  FEE_INSERTION   VARCHAR(20),
  FEE_FINAL       VARCHAR(20),
  FEE_LISTING     VARCHAR(20),
  PIC_XXL         TINYINT,
  PIC_DIASHOW     TINYINT,
  PIC_COUNT       VARCHAR(20),
  ITEM_SITE_ID    VARCHAR(20),
    PRIMARY KEY ( `ARTICLE_NO` ));

The error is now near 'CONDITION VARCHAR(20), REVISED VARCHAR(20), PAYPAL_ACCEPT TI' at line 29

I really can't see what is wrong, does TINYINT need a parameter?

Do MYSQL column names have to be capitals?

Answer 1

"VARCHAR(20)," doesn't assign a name.

Answer 2

watch VARCHAR(20),  **????** VARCHAR(20), 
auction_type VARCHAR(20),

Between watch and auction_type you have missed a column name. Only the varchar(20) is there, either delete that or add in missing column name.

Answer 3

The answer about the missing field name is correct (would vote it up if I had the rep) but I would also recommend placing the following line after 'cat_2_id VARCHAR(20)':

PRIMARY KEY (`article_no`)

Answer 4

From the end:

pic_count VARCHAR(20),item_site_id);

item_site_id doesn't seem to have a type. Give it a type and your query validates...

(Test it here: http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl for finding these kinds of errors)

Answer 5

The formatting.

Answer 6

Tip: Format code for readability.

I take your code and apply simple formatting...

CREATE TABLE auctions (
  article_no        VARCHAR(20),
  article_name      VARCHAR(100),
  subtitle          VARCHAR(20),
  current_bid       VARCHAR(20),
  start_price       VARCHAR(20),
  bid_count         VARCHAR(20),
  quant_total       VARCHAR(20),
  quant_sold        VARCHAR(20),
  start             DATETIME,
  ends              DATETIME,
  origin_end        DATETIME,
  seller_id         VARCHAR(20),
  best_bidder_id    VARCHAR(20),
  finished          VARCHAR(20),
  watch             VARCHAR(20),
  buyitnow_price    VARCHAR(20),
  pic_url           VARCHAR(20),
  private_auction   VARCHAR(20),
  auction_type      VARCHAR(20),
  insert_date       DATETIME,
  update_date       DATETIME,
  cat_1_id          VARCHAR(20),
  cat_2_id          VARCHAR(20),
  article_desc      VARCHAR(20),
  desc_textonly     VARCHAR(20),
  countrycode       VARCHAR(20),
  location          VARCHAR(20),
  condition         VARCHAR(20),
  revised           VARCHAR(20),
  paypal_accept     TINYINT,
  pre_terminated    VARCHAR(20),
  shipping_to       VARCHAR(20),
  fee_insertion     VARCHAR(20),
  fee_final         VARCHAR(20),
  fee_listing       VARCHAR(20),
  pic_xxl           TINYINT,
  pic_diashow       TINYINT,
  pic_count         VARCHAR(20),
  item_site_id
);

...and straight away I can see item_site_id is missing a data type.

Answer 7

I believe the column names "START" and "CONDITION" are 'special' words in MySQL? All I did was simply paste the beautified code into Query Browser and noticed that some column names were 'blue'... :P

Answer 8

According to the list of MySQL Reserved Keywords, CONDITION is a reserved keyword, and you must escape it (using back-ticks) to use it as the name of an object (e.g. table, column, etc.).

I would recommend against using a reserved keyword as a name of a column (even if you escaped it) because that causes all sorts of problems when writing queries in the future.

Answer 9

Check your column names against the list of MySQL reserved words:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

You'll see that CONDITION is a reserved word. You can use MySQL reserved words for column names, but you have to enclose them in back-quotes to clearly tell MySQL that you're not using the word in its conventional use.

. . .
LOCATION    VARCHAR(20),
`CONDITION` VARCHAR(20),
REVISED     VARCHAR(20),
. . .