Create table statement in MYSQL

Question

I am trying to create the following table in MYSQL and is giving errors,

CREATE TABLE CUSTOMER (
CUS_ID INT NOT NULL AUTO_INCREMENT,
CUS_NAME VARCHAR,
CUS_DOB DATE,
CUS_ADDR VARCHAR,
CUS_EMAIL VARCHAR,
CUS_TEL VARCHAR,
CUS_PW VARCHAR,
CUS_JOINDATE DATETIME,
CUS_LASTACCESS DATE)

#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 ' CUS_DOB DATE, CUS_ADDR VARCHAR, CUS_EMAIL VARCHAR, CUS_TEL VARCHAR, CUS_PW' at line 3

Answer 1

You have to specify a length for your VARCHAR fields and also make your AUTO_INCREMENT field a the primary key. I made up lengths here for you - you should consider the maximum length that you actually want for each field.

CREATE TABLE CUSTOMER (
CUS_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
CUS_NAME VARCHAR(30),
CUS_DOB DATE,
CUS_ADDR VARCHAR(50),
CUS_EMAIL VARCHAR(30),
CUS_TEL VARCHAR(20),
CUS_PW VARCHAR(30),
CUS_JOINDATE DATETIME,
CUS_LASTACCESS DATE)

A few other suggestions:

1. Specify the ENGINE (InnoDB or MyISAM)
2. Get rid of the CUS_ prefix to all the fields. You know they are CUSTOMERS. They are in the CUSTOMERS table
3. CUS_PW looks suspiciously like a password field. Do not, under any cirucmstances, ever, in any environment, store customer passwords in plain text in the database. Never, never.

Here is the MySQL Documentation for CREATE TABLE and for the data types

Answer 2

There are two things wrong with it.

Firstly, you need to specify maximum data lengths for the VARCHAR columns.

Secondly, the AUTO_INCREMENT column has to be the PRIMARY KEY.

The following DDL statement will work:

CREATE TABLE CUSTOMER ( 
  CUS_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CUS_NAME VARCHAR(32), 
  CUS_DOB DATE, 
  CUS_ADDR VARCHAR(128), 
  CUS_EMAIL VARCHAR(64), 
  CUS_TEL VARCHAR(16), 
  CUS_PW VARCHAR(32), 
  CUS_JOINDATE DATETIME, 
  CUS_LASTACCESS DATE
);

Obviously you'll need to change the VARCHAR types to reflect the sizes needed for your data.