Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 5 down vote accepted

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

share|improve this answer
    
Thanks for the tips, i shall never ever ever ever store passwords in plain text :) –  Jose David Garcia Llanos Mar 23 '12 at 13:10
    
+1 for point about passwords in plain text –  BryceAtNetwork23 Mar 23 '12 at 13:40

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.

share|improve this answer
    
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) –  Jose David Garcia Llanos 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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