auto_increment causing errors with CREATE TABLE

Question

I have been using netbeans as a tool for my java, and i have a problem. I read this tutorial and then i tried to create a table using this SQL:

CREATE TABLE CUSTOMERS (
    ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(30),
    ADDRESS VARCHAR(30),
    CITY VARCHAR(30),
    STATE_ VARCHAR(30),
    ZIP VARCHAR(15),
    COUNTRY_ID INTEGER,
    PHONE VARCHAR(15),
    EMAIL_ADDRESS VARCHAR(50)
)ENGINE=INNODB;

When i tried to run it, I got this error message:

sql state 42X01 : Syntax error : encountered "AUTO_INCREMENT" at line 2 column 29

and when i delete the AUTO_INCREMENT, another error:

detected ENGINE=INNODB;

can someone help me? Thanks.

Answer 1

my sugestion would be the following

CREATE TABLE CUSTOMERS 
( ID INTEGER NOT NULL auto_increment,
FIRST_NAME VARCHAR(20), 
LAST_NAME VARCHAR(30), 
ADDRESS VARCHAR(30), 
CITY VARCHAR(30), 
STATE_ VARCHAR(30), 
ZIP VARCHAR(15), 
COUNTRY_ID INTEGER, 
PHONE VARCHAR(15), 
EMAIL_ADDRESS VARCHAR(50),
PRIMARY KEY (ID));

Dunno what the engine=innodb is for, have you tried without it?

Answer 2

You seem to be using MySQL syntax with another database engine. The parts it complained about are precisely the MySQL-specific ones.

Answer 3

The "engine=innodb" part specifies the database engine that gets used in the database. With MySQL you can specify different engines like "InnoDB", "MyISAM", etc. They have different properties and features - some allow foreign indexes, some do not. Some have different locking mechanisms, some have different atomicity/rollback properties. I don't know the details but if you need a really high-performance database setup you should investigate which engine is best for each type of table you're creating. Also, all my database experience has been with MySQL and I'm not sure if that's what you're using.

Answer 4

What database engine are you using? How are you accessing it?