3

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
5

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

  • Thanks for the tips, i shall never ever ever ever store passwords in plain text :) – David Garcia Mar 23 '12 at 13:10
5

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.

  • Can't i add the primary key constraint after the creation of all tables like ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUS_ID) – David Garcia Mar 23 '12 at 13:07
  • 1
    Not if you include AUTO_INCREMENT in the CREATE TABLE statement. – Philᵀᴹ Mar 23 '12 at 13:20
  • 2
    @Phil: To be precise, an AUTO_INCREMENT column does not need to be the Primary Key (or part of it). It just needs to be indexed, alhtough I can't imagine a use for that (and it's certainly not the case here). – ypercubeᵀᴹ Mar 23 '12 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.